Jump to Navigation

SQL Server: Select All And Distinct One Column

I came across a request at work where I need to filter my data using the ID Number as a distinct column, show all the other columns (72 columns), and show the latest record. I tried a couple of rounds to get the result needed but to my surprise it was not easy to do it on my SQL server. Maybe because I never had a formal training on it. I searched through the internet for weeks but never found a decent answer. I was able to solve it and I want to share the solution I came up with.

For learning purposes, I put a small portion of my table below. I actually have around 72 columns in my table which obviously makes the DISTINCT function useless given the requirements above.

Table A:

IDNUM FNAME LNAME MOBILE EXPIRATION
1000 JOY HANA 0912345678 5/1/2009
1000 JOY HANA 0918765432 5/1/2010
1000 JOY HANA 0912345677 5/1/2011
1001 SHE HANA 0917775677 5/1/2010
1002 SHE BAY 0917455677 5/1/2011

Desired Result:

IDNUM FNAME LNAME MOBILE EXPIRATION
1000 JOY HANA 0912345677 5/1/2011
1001 SHE HANA 0917775677 5/1/2010
1002 SHE BAY 0917455677 5/1/2011

Below are the SQL statements I used:

-- This is for creating Table A --

DECLARE  @MEMBERS TABLE( IDNUM NVARCHAR(4), FNAME NVARCHAR(10), LNAME NVARCHAR(10), MOBILE NVARCHAR(10), EXPIRATION NVARCHAR(10) )
INSERT INTO @MEMBERS VALUES( '1000','JOY','HANA','0912345678','5/1/2009' );
INSERT INTO @MEMBERS VALUES( '1000','JOY','HANA','0918765432','5/1/2010' );
INSERT INTO @MEMBERS VALUES( '1000','JOY','HANA','0912345677','5/1/2011' );
INSERT INTO @MEMBERS VALUES( '1001','SHE','HANA','0917775677','5/1/2010' );
INSERT INTO @MEMBERS VALUES( '1002','SHE','BAY','0917455677','5/1/2011' );


-- This is the query I used to get the result I needed --

WITH DEDUPE AS (
    SELECT  *
          , ROW_NUMBER() OVER ( PARTITION BY IDNUM ORDER BY CAST(EXPIRATION AS DATE) DESC) AS OCCURENCE
    FROM @MEMBERS 
    )
SELECT  * FROM DEDUPE
WHERE
OCCURENCE = 1

-- Quick explanation --

PARTITION BY IDNUM - This is the column I need as distinct. You can actually put one or more columns here. e.g. PARTITION BY IDNUM, MOBILE, LNAME

ORDER BY CAST(EXPIRATION AS DATE) DESC - This is the sort I used to show the latest record

SELECT  * FROM DEDUPE - This will display all the columns of my table @MEMBERS

OCCURENCE = 1 - Shows the first occurence of the IDNUM

So that's it! Play around with it and hopefully you get the result you need!

Comments

<p>excelent article</p>

Add new comment



Marketing Online