Get top row for every date

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.

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

Select allOpen in new window

>>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 ?