SELECT Query.... - 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)

SELECT Query....

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2009-09-04 : 10:52:45

I have a table called skillsTable it contains fields like skill,date,trshold

skill date trshold
15 2007-01-01 2000
15 2008-03-01 4000
15 2008-08-26 3000
16 2007-01-01 2000
16 2008-03-01 4000
17 2008-08-26 3000

I want to select the trshold for each skill having max(date) like

15 2008-08-26 3000
16 2008-03-01 4000
17 2008-08-26 3000

plz help me to write the query for getting the desired output

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-09-04 : 11:01:12

hint: use row_number()

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-09-04 : 11:13:11

[code]DECLARE @skillsTable TABLE (skill INT, date DATETIME, trshold INT)
INSERT INTO @skillsTable SELECT
15, '2007-01-01', 2000 UNION SELECT
15, '2008-03-01', 4000 UNION SELECT
15, '2008-08-26', 3000 UNION SELECT
16, '2007-01-01', 2000 UNION SELECT
16, '2008-03-01', 4000 UNION SELECT
17, '2008-08-26', 3000

SELECT skill, date, trshold
FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY SKILL ORDER BY date DESC) cnt
FROM @skillsTable
)m
WHERE cnt = 1[/code]

Hope can help...but advise to wait pros with confirmation...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-05 : 03:20:36

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources