While I search to try to find the answer to this I'll ask. I have an Accounts Receivable table. I need to find the last date of the last invoice and the amount of that invoice. If I do Max date and add invoice i get all the invoices. if I do a subquery on date and ID I still get more then one. The date field does not have a time stamp. I'd like group the data by id and then sort by date and get the top row of each ID. I have to fo this for every distinct ID in the AR table.
Please share your query with us and a CREATE TABLE statement for the AR table
SELECT <column_names> FROM ( SELECT *, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY date DESC) AS row_num FROM AR ) AS derived WHERE row_num = 1