Min and group by 2

[sedgely]

i have a table with several fields example of the data is:

f1 f2 f3 f4
0004 01/04/2006 106.3 rp1
0004 01/04/2006 52.1 dp2
0010 01/04/2006 36.2 sp1
0010 01/04/2006 114.3 fr01

i need to return ALL columns but only for the row with the minimum value in column f3. So the result would look like:

f1 f2 f3 f4
0004 01/04/2006 52.1 dp2
0010 01/04/2006 36.2 sp1


Cheers, Craig
Si fractum non sit, noli id reficere

 

[vbkris]

does this help:


select Table.* from
(
select f1,f2,min(f3) f3 from Table group by f1,f2
) ChosenData
inner join
Table
on
Table.f1=ChosenData.f1
Table.f2=ChosenData.f2
Table.f3=ChosenData.f3


assuming table name is Table...

Known is handfull, Unknown is worldfull

 

[Catadmin]

You're going to have to do a min on f4 also if you're wanting that field. Just add it to vbkris's code where it has the subquery (select f1,f2,min(f3)as f3, min(f4) as f4 from Table group by f1,f2).



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[sedgely]

Thanks for the quick responses guys, you've pointed me in the right direction and i've now got it sorted. you both get a star cos this has been driving me mad all morning and it's now sorted. thanks again.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[Catadmin]

NP. Glad we could help. @=)


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[vbkris]

glad we could help dude...

Known is handfull, Unknown is worldfull