How to select unique rows

Question

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

Answer 1

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

Answer 2

ColA ColB ColC

1 A P
2 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.

Answer 3

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?




Answer 4

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