Solved

Get top row for every date

Posted on 2013-11-19
4
306 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
Comment Utility
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
Comment Utility
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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
>>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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now