Select distinct

[Mr. Bart Pietercil]

Hi Ivan,

(write this one on your beer list, I will find a way ;-))

we solved it this way (thanks to your clarification)

select ** from tbl_indicators where recid in (select min(recid) from  
tbl_indicators group by name)


I was a bit surprised that you can use min(recid) without stating it  
in the group by clause.
Can you  explain why this works ?
Because it is a function (min)?

tia

Bart



On 20-mrt-08, at 11:37, Ivan Smahin 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.
>
>
> -- 
> Best regards,
> Ivan Smahin
> Senior Software Engineer
> Paradigma Software, Inc
> Valentina - The Ultra-Fast Database
> http://www.valentina-db.com
>
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina