getting record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

getting record

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-06-02 : 03:49:41

hi,

i'm having results like this:

f1 | f2 | f3
------------
23 | 1 | name1
23 | 2 | name2
23 | 3 | name3
24 | 5 | name3
25 | 6 | name4
25 | 7 | name5

but i would like to get distinct f1 based on min(f2).

how can i do it?

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 03:51:09

SELECT f1,MIN(f2)
FROM Table
GROUP BY f1

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 03:51:50

select *
from tbl t
where f2 = (select min(t2.f2) from tbl t2 where t.f1 = t2.f1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-06-02 : 03:55:56

sorry, i forgot to add, that
i have to count(f1).
If count(f1) > 1 then i should take min(f2).

thank yoiu

nr
SQLTeam MVY

12543 Posts

Posted - 2008-06-02 : 04:11:13

You mean you don't want the row for f1 if there is only one f1?

select *
from tbl t
where f2 = (select min(t2.f2) from tbl t2 where t.f1 = t2.f1)
and f1 in (select f1 from tbl group bt f1 having count(*) > 1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-02 : 04:44:43

quote:
Originally posted by slimt_slimt

sorry, i forgot to add, that
i have to count(f1).
If count(f1) > 1 then i should take min(f2).

thank yoiu

Do you want this?

SELECT f1,MIN(f2)
FROM Table
GROUP BY f1
HAVING COUNT(*) >1

Also it would be better if you could post o/p desired out of the data you posted.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources