Solved

Get top row for every date

Posted on 2013-11-19
4
309 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:countrymeister
  • 2
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39660513
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
 
LVL 73

Expert Comment

by:sdstuber
ID: 39660523
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
 
LVL 35

Expert Comment

by:David Todd
ID: 39661720
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
 
LVL 32

Expert Comment

by:awking00
ID: 39663202
>>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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question