Thread: Converting string to IN query
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.
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
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.
In article <gadsb6$2v2d$1@news.hub.org>,
"Andrus" <kobruleht2@hot.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.
how to return the first record from the sorted records which may have duplicated value.
From
Yi Zhao
Date:
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,
Re: how to return the first record from the sorted records which may have duplicated value.
From
Andreas Kretschmer
Date:
Yi Zhao <yi.zhao@alibaba-inc.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°
Re: how to return the first record from the sorted records which may have duplicated value.
From
Yi Zhao
Date:
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:
-----------
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' loopif 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,
Re: how to return the first record from the sorted records which may have duplicated value.
From
Lennin Caro
Date:
--- On Fri, 9/19/08, Yi Zhao <yi.zhao@alibaba-inc.com> wrote: > From: Yi Zhao <yi.zhao@alibaba-inc.com> > Subject: [GENERAL] how to return the first record from the sorted records which may have duplicated value. > To: "pgsql-general" <pgsql-general@postgresql.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...
Re: how to return the first record from the sorted records which may have duplicated value.
From
Yi Zhao
Date:
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.zhao@alibaba-inc.com> wrote: > > > From: Yi Zhao <yi.zhao@alibaba-inc.com> > > Subject: [GENERAL] how to return the first record from the sorted > records which may have duplicated value. > > To: "pgsql-general" <pgsql-general@postgresql.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... > > > > >
Re: how to return the first record from the sorted records which may have duplicated value.
From
Yi Zhao
Date:
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.zhao@alibaba-inc.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° >