Need help on simple SQL Query - 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

Need help on simple SQL Query

Author

Topic

jag17
Starting Member

4 Posts

Posted - 2008-01-27 : 05:07:21

Hi

I am just starting out on sql programming.

I have one table with three columns

Item Name | Price |Date
Item1 | 44 |25-Jan-08
Item1 | 45 |26-Jan-08
Item1 | 43 |27-Jan-08
Item2 | 21 |26-Jan-08
Item2 | 34 |28-Jan-08
Item3 | 91 |27-Jan-08
Item3 | 98 |29-Jan-08

I want to pick latest price of all items and produce a result as below

Item1 | 43 |27-Jan-08
Item2 | 34 |28-Jan-08
Item3 | 98 |29-Jan-08

Please help me on this

Thank You
-Jag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-27 : 07:41:03

[code]DECLARE @sample TABLE
(
ItemName varchar(5),
Price int,
Date datetime
)
INSERT INTO @sample
SELECT 'Item1' , 44 ,'25-Jan-08' UNION ALL
SELECT 'Item1' , 45 ,'26-Jan-08' UNION ALL
SELECT 'Item1' , 43 ,'27-Jan-08' UNION ALL
SELECT 'Item2' , 21 ,'26-Jan-08' UNION ALL
SELECT 'Item2' , 34 ,'28-Jan-08' UNION ALL
SELECT 'Item3' , 91 ,'27-Jan-08' UNION ALL
SELECT 'Item3' , 98 ,'29-Jan-08'

SELECT s.*
FROM @sample s
INNER JOIN
(
SELECT ItemName, [Date] = MAX([Date])
FROM @sample
GROUP BY ItemName
) m ON s.ItemName = m.ItemName
AND s.[Date] = m.[Date]
ORDER BY s.ItemName

/*
ItemName Price Date
-------- ----------- -----------
Item1 43 2008-01-27
Item2 34 2008-01-28
Item3 98 2008-01-29

(3 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

jag17
Starting Member

4 Posts

Posted - 2008-01-27 : 08:23:04

Excellent. It worked for me.

Thanks a lot for your kind help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 11:24:37

If its sql 2005 you can achieve this with ROW_NUMBER() also:-

SELECT tmp.[Item Name],
tmp.Price,
tmp.Date
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY [Item Name] ORDER BY Date DESC) AS RowNo,
[Item Name],
Price,
Date
FROM Table
)tmp
WHERE tmp.RowNo=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources