Thread: Re: select unique items in db
a schreef: > "select unique id" - i found this line in google search > but i want to make > select * as unique > > select unique * is > or select distinct > > pls point out how to select unique items from a list of million items > Maybe select distinct <fieldlist> from ... or select distinct on (field1,field2) <fieldlist> from ... ? Regards, Stijn.
this doesnt work
SELECT DISTINCT on link *, rank(ts_vec, to_tsquery('default', $qtxt))
FROM feed_entry WHE RE ts_vec @@ to_tsquery('default', $qtxt) ORDER
BY rank(ts_vec, to_tsquery('default', $qtxt)) DESC LIMIT 5 OFFSET 0
can you tell me how to get the DISTINCT elements in LINK
thanks
Stijn Vanroye wrote:
> a schreef:
> > "select unique id" - i found this line in google search
> > but i want to make
> > select * as unique
> >
> > select unique * is
> > or select distinct
> >
> > pls point out how to select unique items from a list of million items
> >
> Maybe
> select distinct <fieldlist> from ...
> or
> select distinct on (field1,field2) <fieldlist> from ... ?
>
> Regards,
>
> Stijn.
a schreef:
> this doesnt work
>
> SELECT DISTINCT on link *, rank(ts_vec, to_tsquery('default', $qtxt))
> FROM feed_entry WHE RE ts_vec @@ to_tsquery('default', $qtxt) ORDER
> BY rank(ts_vec, to_tsquery('default', $qtxt)) DESC LIMIT 5 OFFSET 0
>
> can you tell me how to get the DISTINCT elements in LINK
> thanks
Just place your distinct fields between ().
example syntax:
SELECT DISTINCT ON (field1, field2, fieldn) field1, field4
in your case:
SELECT DISTINCT ON (link) *, rank[...]
Do remember that if you select different fields than the ones you use in
the distinct, the results may not always be what you want.
e.g.:
suppose you have this data loaded in the a table:
id field1 field2
-- ------ ------
1 foo fooble
2 bar barble
3 foo wobble
4 woo wibble
5 foo bobble
if you "select distinct on (field1) *" which of the records with foo in
field1 is going to be returned 1, 3 or 5? I believe this doesn't
nescescarily has to be the same value each time.
a schreef:
> it returns nill now
> or nuthing
> and it is very very very slow
This is not much info. What is the actual query you are using now? What
are the tables like you are running your query against? I'cant really
get a good understanding of your problem. So far I have just given you a
general answer on the question "how do I select unique items in a database".
I Find it odd that a distinct query on a single table would return
NULL/nothing. What is that rank function you are using? That you
experience a speed drop could have a number of reasons. Using a distinct
on a select query could very well decrease perormance, but I can't tell
you in what order of magnitude.
Regards, Stijn.
> Stijn Vanroye wrote:
>> a schreef:
>>> this doesnt work
>>>
>>> SELECT DISTINCT on link *, rank(ts_vec, to_tsquery('default', $qtxt))
>>> FROM feed_entry WHE RE ts_vec @@ to_tsquery('default', $qtxt) ORDER
>>> BY rank(ts_vec, to_tsquery('default', $qtxt)) DESC LIMIT 5 OFFSET 0
>>>
>>> can you tell me how to get the DISTINCT elements in LINK
>>> thanks
>> Just place your distinct fields between ().
>>
>> example syntax:
>> SELECT DISTINCT ON (field1, field2, fieldn) field1, field4
>> in your case:
>> SELECT DISTINCT ON (link) *, rank[...]
>>
>> Do remember that if you select different fields than the ones you use in
>> the distinct, the results may not always be what you want.
>>
>> e.g.:
>>
>> suppose you have this data loaded in the a table:
>>
>> id field1 field2
>> -- ------ ------
>> 1 foo fooble
>> 2 bar barble
>> 3 foo wobble
>> 4 woo wibble
>> 5 foo bobble
>>
>>
>> if you "select distinct on (field1) *" which of the records with foo in
>> field1 is going to be returned 1, 3 or 5? I believe this doesn't
>> nescescarily has to be the same value each time.
>
it returns nill now
or nuthing
and it is very very very slow
Stijn Vanroye wrote:
> a schreef:
> > this doesnt work
> >
> > SELECT DISTINCT on link *, rank(ts_vec, to_tsquery('default', $qtxt))
> > FROM feed_entry WHE RE ts_vec @@ to_tsquery('default', $qtxt) ORDER
> > BY rank(ts_vec, to_tsquery('default', $qtxt)) DESC LIMIT 5 OFFSET 0
> >
> > can you tell me how to get the DISTINCT elements in LINK
> > thanks
>
> Just place your distinct fields between ().
>
> example syntax:
> SELECT DISTINCT ON (field1, field2, fieldn) field1, field4
> in your case:
> SELECT DISTINCT ON (link) *, rank[...]
>
> Do remember that if you select different fields than the ones you use in
> the distinct, the results may not always be what you want.
>
> e.g.:
>
> suppose you have this data loaded in the a table:
>
> id field1 field2
> -- ------ ------
> 1 foo fooble
> 2 bar barble
> 3 foo wobble
> 4 woo wibble
> 5 foo bobble
>
>
> if you "select distinct on (field1) *" which of the records with foo in
> field1 is going to be returned 1, 3 or 5? I believe this doesn't
> nescescarily has to be the same value each time.