Tsql script to select all columns from a table grouped by max column value

Hello,

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:

rownnumber

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:

rownumber1

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:

rownumber outptut

 

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!

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s