find the min - 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)

find the min

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-26 : 08:10:24

i have this table:

OCRSCD	CODE	STATUSH	STATUSL	DATE
2	123	11	11	1.1.09
2	123	12	11	2.1.09
3	123	23	22	3.1.09
2	123	33	22	4.1.09
2	123	33	33	1.1.10

i want to get

OCRSCD	CODE	STATUSH	STATUSL	DATE
3	123	23	22	3.1.09

i mean STATUSL>=22 and the min(date) how can i do it

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 08:20:01

[code]
select OCRSCD, CODE, STATUSH, STATUSL, DATE
FROM
(
select *, row_no = row_number() over (partition by OCRSCD, CODE order by DATE)
from yourtable
where STATUSL >= 22
) d
where row_no = 1
[/code]

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

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-05-26 : 08:35:10

in this query,i wrote in sql 2000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 08:55:05

quote:
Originally posted by inbs

in this query,i wrote in sql 2000

then you should not post in a SQL 2005 forum.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-26 : 08:56:55

[code]
select t.*
from yourtable t
inner join
(
select OCRSCD, CODE, DATE = min(DATE)
from yourtable
where STATUSL >= 22
group by OCRSCD, CODE
) m on t.OCRSCD = m.OCRSCD
and t.CODE = m.CODE
and t.DATE = m.DATE
WHERE t.STATUSL >= 22
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources