Selecting the lowest value - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Selecting the lowest value

Author

Topic

Bill Humphrey
Starting Member

4 Posts

Posted - 2009-05-27 : 04:07:17

How can I select the lowest value for a specific group of records:

ID date callsign timearrive
1 20/10/2009 AAAA 250
1 20/10/2009 BBBB 300
2 20/10/2009 CCCC 267
3 20/10/2009 AAAA 240
3 20/10/2009 CCCC 350

I want to extract all sinle rows and only one of the duplicated rows by id where the timearrive value is the lowest

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-27 : 04:16:26

[code]
select *
from
(
select *,
row_no = row_number() over (partition by ID order by timearrive)
from yourtable
) t
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

ergen
Starting Member

5 Posts

Posted - 2009-05-27 : 04:16:53

this will help http://www.jacek-szarapa.com/index.php?p=sql&d=32

just some changes in fields names will be required, but concept is exactly the same.

----------------
Jacek Szarapa
http://www.jacek-szarapa.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-27 : 04:28:58

See how row_number() can be used for various purposes
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Bill Humphrey
Starting Member

4 Posts

Posted - 2009-05-27 : 10:38:47

Thanks khtan

thats what I was looking for

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources