Re: select unique items in db

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.
>