How to select all first recond for each id

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to select all first recond for each id

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-25 : 13:50:20

t1
id amount flag
1 20 1
1 20 1
1 21 0
2 10 0
2 11 0
3 5 1
3 6 1

t1 is oderd by id and amount
I want
id amount flag
1 20 1
2 10 0
3 5 1
......
id will be unique amount is the smalleat among same id. it is possible two records are the same.
Thank.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-25 : 13:56:48

[code]Select id, min(amount) amount, min(flag) flag
from table
group by id
order by 1,2[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-25 : 14:22:24

use row_number() function

SELECT t.id,
       t.amount,
       t.flag
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY amount) AS RowNo,
id,
amount,
flag
FROM t1
)t
WHERE t.RowNo=1

jeff06
Posting Yak Master

166 Posts

Posted - 2008-03-26 : 07:54:35

Perfect!! Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources