Solved

Get top row for every date

Posted on 2013-11-19
4
307 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

914 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

16 Experts available now in Live!

Get 1:1 Help Now