selecting latest 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

General SQL Server Forums

New to SQL Server Programming

selecting latest date

Author

Topic

krissyM2
Starting Member

4 Posts

Posted - 2009-10-07 : 10:58:21

Hi,

I'm completly new on writing stored procedures so i need some help. I need to select the data from a table. Let's give you an example:

Name Group Date
Kris 1 1-7-2009
Eric 2 10-21-2009
Lisa 1 9-20-2009
Emy 2 8-25-2009

I want to pick out one of each group, i.e. the one with the latest date!!

Appreciate som help asap!

Thanks

/Kris

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-07 : 11:05:13


CREATE TABLE #TEMP (NAME VARCHAR(10), GR_ID INT , DATE DATETIME)

INSERT INTO #TEMP 
SELECT 'Kris', 1, '1-7-2009' UNION ALL
SELECT 'Eric' ,2, '10-21-2009' UNION ALL
SELECT 'Lisa' ,1, '9-20-2009' UNION ALL
SELECT 'Emy' ,2 ,'8-25-2009'


SELECT MAX(DATE) FROM #TEMP GROUP BY GR_ID

DROP TABLE #TEMP

-------------------------
R...

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-07 : 11:06:09

how about this?

select * from
(
select * , row_number() over(partition by [Group] order by [Date] desc) as seq
from <table> 
) t
where t.seq = 1

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-07 : 11:11:06

[code]

SET NOCOUNT ON
GO

CREATE TABLE #t99 ([Name] varchar(10), [Group] int, [Date] datetime)
GO

INSERT INTO #t99 ([Name], [Group], [Date])
SELECT 'Kris', 1, '1-7-2009' UNION ALL
SELECT 'Eric', 2, '10-21-2009' UNION ALL
SELECT 'Lisa', 1, '9-20-2009' UNION ALL
SELECT 'Emy', 2, '8-25-2009'
GO

SELECT *
FROM #t99 o
WHERE EXISTS (
SELECT [Group]
FROM #t99 i
WHERE o.[Group] = i.[Group]
GROUP BY i.[Group]
HAVING MAX(i.[Date]) = o.[Date])
GO

DROP TABLE #t99
GO

SET NOCOUNT ON
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

krissyM2
Starting Member

4 Posts

Posted - 2009-10-07 : 11:32:58

Hi,

Thanks all for quick response. But I don't understand - the table is about 1000 records, how could I list them all when creating the temorary table??

Or what am i missunderstanding?

/kris

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-07 : 11:33:56

It's just sample data...

Just use my query and put the table name you are using in place of #t99 and run it

For example...the output of my query is


Name       Group       Date
---------- ----------- -----------------------
Eric       2           2009-10-21 00:00:00.000
Lisa       1           2009-09-20 00:00:00.000

Would that be correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-07 : 11:35:30

HI

U can use your table instead of temporary table.....

For example


SELECT *
  FROM <Your_table> o
 WHERE EXISTS (
		SELECT [Group]
		  FROM <your_table> i
		 WHERE o.[Group] = i.[Group]
	  GROUP BY i.[Group]
   	    HAVING MAX(i.[Date]) = o.[Date])

-------------------------
R...

krissyM2
Starting Member

4 Posts

Posted - 2009-10-08 : 03:20:10

It worked perfectly!!! I should have understood that you created a table as sample data :-) Well, well. Thanks!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-08 : 04:06:19

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/09/12/return-top-n-rows.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