SQL Server – Select Distinct on just one column when retrieving multiple columns

Select Distinct works fine when you are retrieving just one column.

For example, I have a table called tablea that has two columns called col1 and col2 with two rows. They are:

col1 = January
col2 = 2012

col1 = January
col2 = 2013

Now, for whatever reason, I want only one of these rows, so I say,

Select Distinct col1 from tablea

The result I get back is 1 row, which is what I want. But what if I want to retrieve col2 also? So you try and code,

Select Distinct col1, col2 from tablea

The result is I get back both rows, and that is what I don’t want.
How do we handle selecting distinct on col1, but retrieving col2 also?
Like this:

SELECT  *
FROM (SELECT col1, col2,
      ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RowNumber
         FROM   tablea
         WHERE  somecolumn = 'some value') AS a  --Note: The WHERE clause is optional depending on whether you are doing further filtering
WHERE   a.RowNumber = 1

or if not further filtering on tablea, then like this

SELECT  *
FROM (SELECT col1, col2,
      ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RowNumber
         FROM   tablea) AS a
WHERE   a.RowNumber = 1

The result is again just one row, in fact, the first row seeing I am ordering by col1 and it takes the first row in sequence from the table

January 2012

If you want to add a blank row to the result set, you can add a union statement like this:

select ' ' As col1, ' ' As col2, '1' As RowNumber
union
SELECT  *
FROM (SELECT col1, col2,
      ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col1) AS RowNumber
         FROM   tablea) AS a
WHERE   a.RowNumber = 1
ORDER BY col1

Leave a Reply Cancel reply