SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using Max in a Grouping...		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

qman
Constraint Violating Yak Guru

USA
422 Posts
Posted - 12/14/2012 :  12:33:16  Show Profile  Reply with Quote
I would like to retreive records from a table for based on the maximum date for each type of record.

Example table looks like this

job complete_date note
null 2012-01-01 00:00:00.000 Test 1
null 2012-02-05 00:00:00.000 Test 2
null 2012-12-10 00:00:00.000 Test 3
123 2012-10-11 00:00:00.000 Test 4
123 2012-10-15 00:00:00.000 Test 5
456 2012-12-17 00:00:00.000 Test 6

My results should look like this

job complete_date note
null 2012-12-10 00:00:00.000 Test 3
123 2012-10-15 00:00:00.000 Test 5
456 2012-12-17 00:00:00.000 Test 6

You can see that I want to retreive only each job based on its max completed date. I was thinking of trying to use a max within a gouping, but having not luck

Hope this makes sense, any help would be much appreciated.

Thanks!

sodeep
Flowing Fount of Yak Knowledge

USA
7173 Posts
Posted - 12/14/2012 :  12:47:53  Show Profile  Reply with Quote 
Select * from
(
Select *,ROW_NUMBER() OVER (PARTITION BY ISNULL(Job,'N/A') Order by Completed_date desc)as Seq 
from Table
)Z
Where Z.Seq = 1
Go to Top of Page

qman
Constraint Violating Yak Guru

USA
422 Posts
Posted - 12/14/2012 :  13:57:59  Show Profile  Reply with Quote
Thanks for the help, this work great.
I have never used OVER/PARTITION, need to now figure out how it is working, lol....
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47040 Posts
Posted - 12/14/2012 :  14:25:15  Show Profile  Reply with Quote
quote:
Originally posted by qman

Thanks for the help, this work great.
I have never used OVER/PARTITION, need to now figure out how it is working, lol....




see uses of Row_number function

http://beyondrelational.com/modules/2/blogs/70/posts/10802/multipurpose-rownumber-function.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000