Getting recent date - 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)

Getting recent date

Author

Topic

sarakmo
Starting Member

7 Posts

Posted - 2007-09-13 : 13:34:10

Greetings,

I'm trying to retrieve the most recent date for each code.

for example:
Table A
code Date
code111: 02/10/2005
code111: 03/10/2006
code111: 03/10/2007
code122: 04/10/2000
code122: 03/10/2003
code122: 03/10/2007

I'm trying to retrieve the most recent date for each code and use it in a large query.

Right now I've tried dumping my MAX(date) results in a temp table...but obviously the 'max' date in this case returns the only date it has.

Please help!

Appreciate it greatly.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-13 : 13:36:26

explore group by

--------------------
keeping it simple...

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-09-13 : 13:42:02

SELECT Code,MAX(DATE)
FROM TableA
GROUP BY Code

Aggregate Functions would be good to look at as well

Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 14:32:09

SELECT Code, Date FROM (
SELECT Code, Date, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Date DESC) AS RecID FROM Table1
) AS d WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-09-13 : 15:19:15

Peso -- you love those ranking functions!!!!

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:21:28

Yes. They seem to be resource friendly too.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-14 : 02:21:59

quote:
Originally posted by Peso

SELECT Code, Date FROM (
SELECT Code, Date, ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Date DESC) AS RecID FROM Table1
) AS d WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

But I prefer using this approach for selecting Top N data fro each group

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources