Selecting only records with most recent date

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 only records with most recent date

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-15 : 13:29:23

Using MS SQL 2005, how can I SELECT only the records whos date field is equal to the most recent date held in that field?

For example:

date item colour
---- ---- ------
12/15/2007 shirt red
12/13/2007 shirt black
12/15/2007 shirt blue

result(s) expected:

12/15/2007 shirt red
12/15/2007 shirt blue

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-15 : 14:01:46

Select [Date], Item, Color
FROM [Table]
Where [Date] = (Select Max([Date]) From [Table])

is one way...

Poor planning on your part does not constitute an emergency on my part.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-12-15 : 14:29:16

Many thanks dataguru1971. I'd been using a SELECT query which didn't include a SELECT within the WHERE section.

Learned something new ... :)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-15 : 15:48:38

You are welcome, but that is but just one way to skin the cat.

Poor planning on your part does not constitute an emergency on my part.

doco
Yak Posting Veteran

77 Posts

Posted - 2007-12-15 : 17:22:19

What if I wanted the max date of each category

cat_id date item color
-------------------------------
12 12/14/2007 Shirt black
13 12/14/2007 Shirt white
14 12/14/2007 Shirt green
12 12/15/2007 Shirt black
13 12/19/2007 Shirt white
14 12/21/2007 Shirt green

I want a return of

cat_id date item color
------------------------------
12 12/15/2007 Shirt black
13 12/19/2007 Shirt white
14 12/21/2007 Shirt green

Latest date for each cat_id?

The columns don't line up but you get the idea...

doco

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-12-15 : 17:42:09

select cat_id,item,color,max(date) from yourtable
group by cat_id,item,color

this table seems not to be normalized btw. does 14 always mean (shirt,green)?

elsasoft.org

JasonL
Starting Member

35 Posts

Posted - 2007-12-16 : 00:17:35

>> Select [Date], Item, Color
FROM [Table]
Where [Date] = (Select Max([Date]) From [Table])

Make sure sekect ...
from table X
where [date] = select max([data] from table Y
where y.primary_key = x.primary_key)
otherwise you will get wrong results !!!!

JasonL

doco
Yak Posting Veteran

77 Posts

Posted - 2007-12-16 : 06:01:40

quote:

this table seems not to be normalized btw. does 14 always mean (shirt,green)?

Yes. This is a ficticious table of course so cat_id would not be PK; and the table ( in this case ) does not have a PK. The issue here is not normalization but to find all cat_id at the latest date for each cat_id.

So, JasonL. What you are saying is


select
      x.date
    , x.item
    , x.color
from 
      items_table x
    , items_table y
where
    x.cat_id = y.cat_id and  --  legacy join ?
    x.date = max( y.date )

Guess I'll have to create a temp table and test it ...

doco

JasonL
Starting Member

35 Posts

Posted - 2007-12-17 : 11:09:36

No, you don't have to create a temp table.
Referring to my query or Jezemine. We are basically saying:
"For each primary key return me only the row with the max date"

It is the same sql as what jezemine has stated. I just merely point out that you WILL need the co-related subquery using the primary key.

HTH
JasonL
http://ushipoisv.spaces.live.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-17 : 12:42:11

To help illistrate what JasonL is saying here is an example:

DECLARE @Yak TABLE (cat_id INT, date SMALLDATETIME, item VARCHAR(50), color VARCHAR(50))

INSERT @Yak
SELECT 12, '12/14/2007', 'Shirt', 'black'
UNION ALL SELECT 13, '12/14/2007', 'Shirt', 'white'
UNION ALL SELECT 14, '12/14/2007', 'Shirt', 'green'
UNION ALL SELECT 12, '12/15/2007', 'Shirt', 'black'
UNION ALL SELECT 13, '12/19/2007', 'Shirt', 'white'
UNION ALL SELECT 14, '12/21/2007', 'Shirt', 'green'

-- Works
SELECT
	cat_id,
	MAX(date),
	item,
	color
FROM
	@Yak
GROUP BY
	cat_id,
	item,
	color

-- Doesn't work because the Max date is over the entire table, in this case 12/21/2007
SELECT
	cat_id,
	date,
	item,
	color
FROM 
	@Yak
WHERE
	date = (SELECT MAX(date) FROM @yak)

-- Works because MAX(date) is correlated.
SELECT
	cat_id,
	date,
	item,
	color
FROM 
	@Yak AS Yak
WHERE
	date = (SELECT MAX(date) FROM @Yak AS T WHERE T.cat_id = Yak.cat_id)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-17 : 16:48:56

The original question was to produce results based on the most recent date in the column. Obviously that original response of mine wouldn't work for the rephrased question for max date for each category and the correlated sub-query is more appropriate as well described above.

Thanks for the follow ups.

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 16:54:34

Since you are using SQL Server 2005, this might be a solution for you?

DECLARE @Yak TABLE (cat_id INT, date SMALLDATETIME, item VARCHAR(50), color VARCHAR(50))

INSERT @Yak
SELECT 12, '12/14/2007', 'Shirt', 'black'
UNION ALL SELECT 13, '12/14/2007', 'Shirt', 'white'
UNION ALL SELECT 14, '12/14/2007', 'Shirt', 'green'
UNION ALL SELECT 12, '12/15/2007', 'Shirt', 'black'
UNION ALL SELECT 13, '12/19/2007', 'Shirt', 'white'
UNION ALL SELECT 14, '12/21/2007', 'Shirt', 'green'

SELECT	cat_id,
	date,
	item,
	color
FROM	(
		SELECT	cat_id,
			date,
			item,
			color,
			ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY date DESC) AS RecID
		FROM	@Yak
	) AS d
WHERE	RecID = 1

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources