Menu
I have the following table in MSSQL2005
id | business_key | result
1 | 1 | 0
2 | 1 | 1
3 | 2 | 1
4 | 3 | 1
5 | 4 | 1
6 | 4 | 0
And now i want to group based on the business_key returning the complete entry with the highest id. So my expected result is:
business_key | result
1 | 1
2 | 1
3 | 1
4 | 0
I bet that there is a way to achieve that, i just can't see it at the moment.
581
Retrieving the last record in each group using GROUP BY There are two solutions explained here using the GROUP BY clause. In both these solutions, we will be using the MAX() function to get the maximum value of id and then retrieving the other columns corresponding to this maximum id.
Let's say you want to get last record in each group, that is, for each product. First we use GROUP BY to get most recent date for each group. Now that we know the most recent date for each group, we join this result with our original table to get latest record by date group.
How do I SELECT the first and last row in a GROUP BY in SQL? To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ).
To get the first and last record, use UNION. LIMIT is also used to get the number of records you want.
An alternative solution, which may give you better performance (test both ways and check the execution plans):
SELECT
T1.id,
T1.business_key,
T1.result
FROM
dbo.My_Table T1
LEFT OUTER JOIN dbo.My_Table T2 ON
T2.business_key = T1.business_key AND
T2.id > T1.id
WHERE
T2.id IS NULL
This query assumes that the ID is a unique value (at least for any given business_key) and that it is set to NOT NULL.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
