How to select unique rows

# How to select unique rows

• Thursday, January 31, 2013 3:31 AM

What is the best and most efficient way to select distinct values from the following table:

COlA ColB  ColC

1 A P
2 B Q
2 C R
3 D S
3 E T
4 F U

Out of the above rows I only want rows where ColA is unique. So it should return 4 rows in total. I am aware of using the ROW_NUMBER function, group by function but I want to know what the most efficient way is?

KK

### All Replies

• Thursday, January 31, 2013 3:35 AM
Moderator

Your solution depends on the desired result - can you show it?

In any case, you need to test both queries to decide which one suits your needs and more efficient.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

• Thursday, January 31, 2013 3:50 AM

ColA ColB ColC

1 A P
B  Q
3 D S
4 F U

In this particular case I don't care which row is selected of the 2 duplicates. Also I don't have to use group by or row_number. Those are the 2 options I know of. I am interested in finding out if there is any other better solution to this.

• Edited by Thursday, January 31, 2013 3:51 AM
•
• Thursday, January 31, 2013 4:01 AM

IDK if it is the most efficient but it works and it uses t-SQL functions so it has to be fast, right? :)

I tested with duplicate rows and only one row was returned.  If you are finding otherwise then you can a primary autoincrement int column and instead of "order by ColA" do "order by [ident]```". ```

```select * from
(
SELECT ColA,ColB, ColC, Rank() OVER(PARTITION BY ColA Order by ColA) as Rank
from myTable
) t
where Rank = 1```

Does that work for you?

cs

does this work?

``` ```

• Thursday, January 31, 2013 12:53 PM
Moderator

I have a blog post comparing different ways to achieve the result (the third in my links). But each case is different and you will be in the position to test and chose the best solution:

Optimizing TOP N per Group Queries - blog by Itzik Ben-Gan explaining various optimization ideas
Including an Aggregated Column's Related Values - Erik and mine blog presenting several solutions of the problem with explanations for each
Including an Aggregated Column's Related Values - Part 2 - my blog post with use cases for the previous blog

For every expert, there is an equal and opposite expert. - Becker's Law

My blog