SQLTeam.com | Weblogs | Forums

Last row of records sorted by date and grouped bu custid


#1

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.


#2

Please share your query with us and a CREATE TABLE statement for the AR table


#3
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