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

Sql Query

Author

Topic

miteshvaidya
Starting Member

1 Post

Posted - 2015-03-17 : 07:02:31

I have the below data

Location Item Date Stock
LOC1 SUGAR 1-1-15 300
LOC1 SUGAR 5-1-15 150
LOC2 SUGAR 2-1-15 500
LOC2 SUGAR 10-1-15 450
LOC1 RICE 6-1-15 50
LOC1 RICE 8-1-15 60

I WANT THE LOCATION, ITEM, LAST DATE, STOCK

The result should be as as below
Location Item Date Stock
LOC1 SUGAR 5-1-15 150
LOC2 SUGAR 10-1-15 450
LOC1 RICE 8-1-15 60

can anyone help me to write query for the above.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-17 : 08:50:25

Try this

select Location, Item, Date, Stock from
(
select row_number() over (partition by Location Order by Location) as sno, * from table
) as t
where sno=2

Madhivanan

Failing to plan is Planning to fail

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 09:14:23

quote:
Originally posted by madhivanan

Try this

select Location, Item, Date, Stock from
(
select row_number() over (partition by Location Order by Location) as sno, * from table
) as t
where sno=2

Does not return the correct results

magbenyezi
Starting Member

3 Posts

Posted - 2015-03-17 : 09:18:05

;WITH myCTE as(
SELECT t.Location, t.Item, t.[Date], t.Stock, DENSE_RANK() OVER(ORDER BY t.[Date] DESC) AS myrank
FROM YourTable t
)SELECT *
FROM myCTE WHERE myCTE.myrank =1

MA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 09:20:55

This works:


Select a.Location, a.Item, b.Lastdate, a.Stock
from table a
join 
(
	select Location, Item, max([date]) as Lastdate
	from @
	group by Location, Item
) b

on a.Location = b.Location and a.Item = b.Item and a.[Date] = b.LastDate

as does this:


select Location, Item, [Date] as [Last Date], Stock
from 
(
	select Location, Item, [Date], Stock
		 , max([date]) over(partition by Location, Item) as  Lastdate  
	from   table
) _

You'll need to test them both to figure out which one performs best for you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources