Re: how to return the first record from the sorted records which may have duplicated value.

String contains list of document numbers (integers) like:

'1,3,4'

How to SELECT documents whose numbers are contained in this string.
I tried

create temp table invoices ( invoiceno int );
insert into invoices values (1);
insert into invoices values (2);
insert into invoices values (3);
insert into invoices values (4);
SELECT * FROM invoices WHERE invoiceno IN ( '1,3,4' );

but this causes error.

Numbers should be passed as single string literal since FYIReporting
RDLEngine does not allow multivalue parameters.

How to fix this so that query returns invoices whose numbers are contained
in string literal ?
Can arrays used to convert string to list or any other solution ?

Andrus.

I found that following query works:

create temp table test ( test int ) on commit drop;
insert into test values(1);
select * from test where test = ANY ( '{1,2}' );

Is this best solution ?

Will it work without causing stack overflow with 8.2 server default
settings
if string contains some thousands numbers ?

I have found that IN (1,2,...) causes stack overflow in server if there are
large number of items in list.

Andrus.

Andrus escreveu:
> <snip>
> SELECT * FROM invoices WHERE invoiceno IN ( '1,3,4' );
>
> but this causes error.
> <snip>

change it to ( '1','3','4' ) or ( 1,3,4 )

--

ACV

Andrus wrote:
> String contains list of document numbers (integers) like:
>
> '1,3,4'
>
> How to SELECT documents whose numbers are contained in this string.

> Numbers should be passed as single string literal since FYIReporting
> RDLEngine does not allow multivalue parameters.

Hmm - might be worth bringing that to their attention.

Try string_to_array(). Example:

SELECT * FROM generate_series(1,10) s
WHERE s = ANY (string_to_array('1,3,5,7', ',')::int[]);

Note that I'm casting it to an array of integers so the "= ANY" knows
what types it will need to match.

--
Richard Huxton
Archonet Ltd

On Fri, Sep 12, 2008 at 04:04:18PM +0100, Richard Huxton wrote:
> Andrus wrote:
> > String contains list of document numbers (integers) like:
> >
> > '1,3,4'
> >
> > How to SELECT documents whose numbers are contained in this string.
>
> > Numbers should be passed as single string literal since FYIReporting
> > RDLEngine does not allow multivalue parameters.
>
> Hmm - might be worth bringing that to their attention.

I'm probably missing something, but does PG?

> Try string_to_array(). Example:
>
> SELECT * FROM generate_series(1,10) s
> WHERE s = ANY (string_to_array('1,3,5,7', ',')::int[]);

I don't think you need the string_to_array function call, an array
literal should do perfectly well here. I.e.

WHERE s = ANY ('{1,2,3}'::int[]);

the literal can of course be a parameter as well:

WHERE s = ANY ($1::int[]);

Sam

In article <gadsb6$2v2d$1(at)news(dot)hub(dot)org>,
"Andrus" <kobruleht2(at)hot(dot)ee> writes:

> I found that following query works:
> create temp table test ( test int ) on commit drop;
> insert into test values(1);
> select * from test where test = ANY ( '{1,2}' );

> Is this best solution ?

> Will it work without causing stack overflow with 8.2 server default
> settings
> if string contains some thousands numbers ?

If you get thousands of numbers, it is safer and maybe also faster to
put them into a temporary table, analyze it, and then join it to the
table in question.

hi all:
I have a table with columns(>2) named "query", "pop", "dfk".
what I want is:
when I do some select, if the column "query" in result records have
duplicate value, I only want the record which have the maximum value of
the "pop".

for example, the content of table:
query pop dfk
-----------------------
abc 30 1 --max
foo 20 lk --max
def 16 kj --max
foo 15 fk --discard
abc 10 2 --discard
bar 8 are --max

the result should be:
query pop dfk
-----------------------
abc 30 1
foo 20 lk
def 16 kj
bar 8 are

now, I do it like this(plpgsql)
------------------------------------
declare hq := ''::hstore;
begin
for rc in execute 'select * from test order by pop desc' loop
if not defined(hq, rc.query) then
hq := hq || (rc.query => '1')::hstore;
return next rc;
end if;
end loop;
-----------------------------------
language sql/plpgsql will be ok.

ps: I try to use "group by" or "max" function, because of the
multi-columns(more than 2), I failed.

thanks,
any answer is appreciated.

regards,

Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> schrieb:

> hi all:
> I have a table with columns(>2) named "query", "pop", "dfk".
> what I want is:
> when I do some select, if the column "query" in result records have
> duplicate value, I only want the record which have the maximum value of
> the "pop".
>
> for example, the content of table:
> query pop dfk
> -----------------------
> abc 30 1 --max
> foo 20 lk --max
> def 16 kj --max
> foo 15 fk --discard
> abc 10 2 --discard
> bar 8 are --max
>
> the result should be:
> query pop dfk
> -----------------------
> abc 30 1
> foo 20 lk
> def 16 kj
> bar 8 are

test=*# select * from d;
query | pop | dfk
-------+-----+-----
abc | 30 | 1
foo | 20 | lk
def | 16 | kj
foo | 15 | fk
abc | 10 | 2
bar | 8 | are
(6 Zeilen)

Zeit: 0,213 ms
test=*# select distinct on (query) * from d order by query, pop desc;
query | pop | dfk
-------+-----+-----
abc | 30 | 1
bar | 8 | are
def | 16 | kj
foo | 20 | lk
(4 Zeilen)

Hint: distinct on isn't standard-sql, it's an PG-extension.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

now, I do it like this(plpgsql)
-----------
this methold have low efficiency, when the records is large, it will
become slow, so someone can tell me some high efficiency way???

thanks.
On Fri, 2008-09-19 at 16:51 +0800, Yi Zhao wrote:

> hi all:
> I have a table with columns(>2) named "query", "pop", "dfk".
> what I want is:
> when I do some select, if the column "query" in result records have
> duplicate value, I only want the record which have the maximum value of
> the "pop".
>
> for example, the content of table:
> query pop dfk
> -----------------------
> abc 30 1 --max
> foo 20 lk --max
> def 16 kj --max
> foo 15 fk --discard
> abc 10 2 --discard
> bar 8 are --max
>
> the result should be:
> query pop dfk
> -----------------------
> abc 30 1
> foo 20 lk
> def 16 kj
> bar 8 are
>
> now, I do it like this(plpgsql)
> ------------------------------------
> declare hq := ''::hstore;
> begin
> for rc in execute 'select * from test order by pop desc' loop
> if not defined(hq, rc.query) then
> hq := hq || (rc.query => '1')::hstore;
> return next rc;
> end if;
> end loop;
> -----------------------------------
> language sql/plpgsql will be ok.
>
> ps: I try to use "group by" or "max" function, because of the
> multi-columns(more than 2), I failed.
>
> thanks,
> any answer is appreciated.
>
> regards,
>
>

--- On Fri, 9/19/08, Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> wrote:

> From: Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com>
> Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value.
> To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
> Date: Friday, September 19, 2008, 8:51 AM
> hi all:
> I have a table with columns(>2) named "query",
> "pop", "dfk".
> what I want is:
> when I do some select, if the column "query" in
> result records have
> duplicate value, I only want the record which have the
> maximum value of
> the "pop".
>
> for example, the content of table:
> query pop dfk
> -----------------------
> abc 30 1 --max
> foo 20 lk --max
> def 16 kj --max
> foo 15 fk --discard
> abc 10 2 --discard
> bar 8 are --max
>
> the result should be:
> query pop dfk
> -----------------------
> abc 30 1
> foo 20 lk
> def 16 kj
> bar 8 are
>
> now, I do it like this(plpgsql)
> ------------------------------------
> declare hq := ''::hstore;
> begin
> for rc in execute 'select * from test order by pop
> desc' loop
> if not defined(hq, rc.query) then
> hq := hq || (rc.query => '1')::hstore;
> return next rc;
> end if;
> end loop;
> -----------------------------------
> language sql/plpgsql will be ok.
>
> ps: I try to use "group by" or "max"
> function, because of the
> multi-columns(more than 2), I failed.
>
> thanks,
> any answer is appreciated.
>
> regards,
>

this query work for me....

select distinct max(pop),query from test
group by query

please reply your results

thanks...

yes, 
> select distinct max(pop),query from test
> group by query
test=# select distinct max(pop),query from bar group by query;
max | query
-----+-------
8 | bar
16 | def
20 | foo
30 | abc

but, I want to get the records contains more than two columns(max,
query, "dfk"), so, if I use group by, max, distinct keywords, I should
use this sql and get the result as below:
test=# select distinct max(pop),query, dfk from bar group by query, dfk;
max | query | dfk
-----+-------+-----
8 | bar | are
10 | abc | 2
15 | foo | fk
16 | def | kj
20 | foo | lk
30 | abc | 1

btw: *distinct on* is useful:)

thanks,

On Fri, 2008-09-19 at 09:00 -0700, Lennin Caro wrote:
>
>
> --- On Fri, 9/19/08, Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> wrote:
>
> > From: Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com>
> > Subject: [GENERAL] how to return the first record from the sorted
> records which may have duplicated value.
> > To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
> > Date: Friday, September 19, 2008, 8:51 AM
> > hi all:
> > I have a table with columns(>2) named "query",
> > "pop", "dfk".
> > what I want is:
> > when I do some select, if the column "query" in
> > result records have
> > duplicate value, I only want the record which have the
> > maximum value of
> > the "pop".
> >
> > for example, the content of table:
> > query pop dfk
> > -----------------------
> > abc 30 1 --max
> > foo 20 lk --max
> > def 16 kj --max
> > foo 15 fk --discard
> > abc 10 2 --discard
> > bar 8 are --max
> >
> > the result should be:
> > query pop dfk
> > -----------------------
> > abc 30 1
> > foo 20 lk
> > def 16 kj
> > bar 8 are
> >
> > now, I do it like this(plpgsql)
> > ------------------------------------
> > declare hq := ''::hstore;
> > begin
> > for rc in execute 'select * from test order by pop
> > desc' loop
> > if not defined(hq, rc.query) then
> > hq := hq || (rc.query => '1')::hstore;
> > return next rc;
> > end if;
> > end loop;
> > -----------------------------------
> > language sql/plpgsql will be ok.
> >
> > ps: I try to use "group by" or "max"
> > function, because of the
> > multi-columns(more than 2), I failed.
> >
> > thanks,
> > any answer is appreciated.
> >
> > regards,
> >
>
>
> this query work for me....
>
>
> select distinct max(pop),query from test
> group by query
>
>
> please reply your results
>
> thanks...
>
>
>
>
>

yes, thanks u very much, it's work:)

regards,
Yi
On Fri, 2008-09-19 at 11:06 +0200, Andreas Kretschmer wrote:
> Yi Zhao <yi(dot)zhao(at)alibaba-inc(dot)com> schrieb:
>
> > hi all:
> > I have a table with columns(>2) named "query", "pop", "dfk".
> > what I want is:
> > when I do some select, if the column "query" in result records have
> > duplicate value, I only want the record which have the maximum value of
> > the "pop".
> >
> > for example, the content of table:
> > query pop dfk
> > -----------------------
> > abc 30 1 --max
> > foo 20 lk --max
> > def 16 kj --max
> > foo 15 fk --discard
> > abc 10 2 --discard
> > bar 8 are --max
> >
> > the result should be:
> > query pop dfk
> > -----------------------
> > abc 30 1
> > foo 20 lk
> > def 16 kj
> > bar 8 are
>
> test=*# select * from d;
> query | pop | dfk
> -------+-----+-----
> abc | 30 | 1
> foo | 20 | lk
> def | 16 | kj
> foo | 15 | fk
> abc | 10 | 2
> bar | 8 | are
> (6 Zeilen)
>
> Zeit: 0,213 ms
> test=*# select distinct on (query) * from d order by query, pop desc;
> query | pop | dfk
> -------+-----+-----
> abc | 30 | 1
> bar | 8 | are
> def | 16 | kj
> foo | 20 | lk
> (4 Zeilen)
>
> Hint: distinct on isn't standard-sql, it's an PG-extension.
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect. (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly." (unknown)
> Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
>