Get top row for every date

Have a Table Product

RunID
Date
Qty
Price




1 , 11/15/2013, 10, 11
2,  11/15/2013 , 12, 13
3, 11/15/2013, 13. 18


11 , 11/16/2013, 5, 4
21,  11/16/2013 , 1,5
13, 11/16/2013, 3. 6


For every date I need the highest RunID and the relevant Price and Quantity

This should return

3, 11/15/2013, 13, 18
21,11/16/2013, 3, 6
LVL 1
countrymeisterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sdstuberConnect With a Mentor Commented:
select runid,date,qty,price from
(select RunID,
Date,
Qty,
Price, row_number() over(partition by date order by runid desc) as rn from your_table) as x
where rn =1
0
 
sdstuberCommented:
you could also do it with double querying the table which, of course, seems kind of silly since you don't really need to access the table twice

select runid,date,qty,price from your_table
where runid in (select max(runid) from your_table group by date)

but,  try it both ways depending on your specific indexing and cpu/memory available for sorting, this might still be faster.
0
 
David ToddSenior DBACommented:
Hi,

Here is a more generic case. Show the last row on each order. This could be to find the last transaction for each client, the last price for each fund, when most funds aren't priced on the weekend, or some funds are priced infrequently vs some a priced daily, etc.

HTH
  David

-- table
select *
from dbo.DropShipDetail d
order by
	d.DropShipOrderID
;

-- results
DropShipOrderID DropShipRowID DropShipQty GiftProductCodeID
--------------- ------------- ----------- -----------------
1234            1             1           book1
1423            1             2           case1
7790            1             1           handy
7790            2             1           book1
7790            3             1           case1
8899            1             1           book1
8899            2             2           case1
9934            1             2           book1

-- query to return the last row on each order
select 
	d.*
from dbo.DropShipDetail d
inner join (
	select
		di.DropShipOrderID
		, max( di.DropShipRowID ) as MaxRowID
	from dbo.DropShipDetail di
	group by
		di.DropShipOrderID
	) dd
	on dd.DropShipOrderID = d.DropShipOrderID
	and dd.MaxRowID = d.DropShipRowID
order by
	d.DropShipOrderID
;

-- results
DropShipOrderID DropShipRowID DropShipQty GiftProductCodeID
--------------- ------------- ----------- -----------------
1234            1             1           book1
1423            1             2           case1
7790            3             1           case1
8899            2             2           case1
9934            1             2           book1

Open in new window

0
 
awking00Commented:
>>This should return<<
3, 11/15/2013, 13, 18
21,11/16/2013, 3, 6  ==> shouldn't this be 21,11/16/2013, 1, 5 ?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.