In this blog I would like to post the t-sql statement to select all columns from a table grouped by max value of a column. This is possible by using the row_number function.
Below is the sample data for which I wrote the script:
My output should be only the highlighted columns from the above image. Below is the script that I wrote to select the accountid, endingdate and contractid with the most recent endingdate (or max endingdate)
with mycte as ( SELECT ACCOUNTID,ENDINGDATE,CONTRACTID,row_number() Over(partition by ACCOUNTID Order by ENDINGDATE DESC )rn FROM TABLE1 ) SELECT ACCOUNTID, ENDINGDATE, CONTRACTID from mycte Where rn=1 order by ENDINGDATE desc
As specified in the script I am using the row_number() function and partitioning on the accountid since the accountid is the column for uniqueness and ordering by the endingdate as endingdate is the column for which I want to get the maximum value. The select statement in the first part of the script returns the following output:
If you observe the output above the rownumbers are assigned to the accountid’s with 1 for the max endingdate column for each accountid. So the second select statement selects only those endingdate columns with rows whose rownumbers are 1. Here is the final output:
However if you want to select records with a column with least value then you need to remove the DESC (descending) after the order by in the first select statement of the script.
Hope this helps!