Select distinct

[Ivan Smahin]

Hello Ivan,

Thursday, March 20, 2008, 12:37:14 PM, you wrote:

> Hello Mr.,

> Thursday, March 20, 2008, 12:13:26 PM, you wrote:

>> Hi List, Ruslan

>> struggling with this query which seems simple enough

>> the query

>> select distinct name from tbl_indicators returns the records I want.
>> However in this selection I want to see all fields from the table so I
>> (mistakenly) thought I could do

>> select distinct name, recid from tbl_indicators

>> but this returns all records. I can see why but I don't see how I can
>> resolve my problem

> select distinct f1, min(recID) from t1 group by f1

> Thinking of it - "distinct" gets only single record from the set of
> records with equal values. If we want to select another fields we
> want to get "any" record from the set of records with the same f1.
> So, we have to define "the rule" which record must be treated as
> "any" :)

> So you should use min(), max() and others aggregating functions.

It is only pattern because you can easily see the lack:
If you want to get two or more fields and selected values must belong to
the same record.
I mean - you can not do it this way:

select distinct f1, min(f1), min(f2) ....


But you can use subqueries - something like this:


select
      *
from
    T1
where
     recID in(
           select
                 rec
           from
               (select
                      DISTINCT f1,
                      min(recID) as rec
               from
                   t1
               group by
                     f1) )

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com