rai nalasa rai nalasa - 3 months ago 8
SQL Question

Select latest Rows by date and time

I have a large of transctions daily on my table.
I wanted to only get the last transaction happened on that day.

I'm tying to copy this but I can't implement it on my own

here is my script

SELECT
product_id
,product_name
,stock_on_hand
,stock_in
,stock_out
,date_track
FROM stocks_history
WHERE
product_id = 30
AND
date_track between '9/1/16' and '9/9/16'


and this is my result as of the moment

enter image description here

this is my desired output

enter image description here

how could I get only the last transaction happened on that day using datetime only?

Answer

If you have a column that specifies the ordering within a day, then you can do:

SELECT sh.*
FROM (SELECT sh.*,
             ROW_NUMBER() OVER (PARTITION BY product_id, CAST(date_track as DATE)
                                ORDER BY date_track DESC
                               ) as seqnum
      FROM stocks_history
      WHERE product_id = 30 AND 
            date_track between '2016-09-02' and '2016-09-05'
     ) sh
WHERE seqnum = 1;

If you have another column that has the ordering, then use that for the ORDER BY.