distinct rows containing the max value in a particular colum

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 2000 Forums

Transact-SQL (2000)

distinct rows containing the max value in a particular colum

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-09 : 09:31:55

sankaran writes "i have a table containing itemname, date & location. itemname repeats with different location and date values. i need to extract distinct items with their location on latest date. any help?

thnks & rgds
k.s.unni."

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-09 : 09:37:20

select t1.itemname, t1.location, t1.date
from table t1
where date =
(select max(t2.date)
from table t2
where t2.itemname = t1.itemname)

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-09 : 09:42:46

[code]
SELECT mt.ItemName,
mt.[Date],
mt.Location
FROM MyTable AS mt
JOIN (SELECT mt.ItemName,
MAX(mt.[Date]) AS [Date]
FROM MyTable AS mt
GROUP BY mt.ItemName
) AS MaxDate
ON mt.ItemName = MaxDate.ItemName
AND mt.[Date] = MaxDate.[Date]
[/code]

Mark

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-09 : 09:47:38

What should happen when there are 2 records as follows:
1. itemA, 2006-8-9, Loc1
~~2. itemB, 2006-8-9, Loc2~~
2. itemB, 2006-8-9, Loc2
Which of these records is the latest??? Maybe you need some more data. Or maybe this is not possible in your case...

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-08-09 : 09:56:42

Hi Q,
I'm assuming that those two rows are both meant to have the same itemname...

Mark

Q
Yak Posting Veteran

76 Posts

Posted - 2006-08-09 : 09:59:28

Yes, you're right...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 10:00:04

sankaran, post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail