Обсуждение: 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.