sql selecting unique rows based on a specific column

Question

I have an table like this :

Col1    Col2    Col3    Col4
asasa   1          d    44
asasa   2         sd    34
asasa   3          f    3
dssd    4          d    2
sdsdsd  5         sd    11
dssd    1         dd    34
xxxsdsds2          d    3
erewer  3          sd   3

I am trying to filter out something like this based on Col1

Col1    Col2    Col3    Col4
asasa   1          d    44
dssd    4          d    2
sdsdsd  5         sd    11
xxxsdsds2          d    3
erewer  3         sd    3

I am trying to get the all unique rows based on the values in Col1. If I have duplicates in Col1, the first row should be taken.

I tried SELECT Col1 FROM tblname GROUP BY Col1 and got unique Col1 but extending it using * is giving me error.

The other columns are not available because they are not in the group by. It also doesn't make sense to have them because they are different. So your example result shows the first record of each group — Simon Martinelli, Aug 04 '21 at 13:09
Every row is unique But Col1 will have duplicates. I want filter out the unique rows based on Col1 — VJ D, Aug 04 '21 at 13:10
Which RDBMS are you using? The solution might depend on which platform/SQL dialect is used. — SchmitzIT, Aug 04 '21 at 13:10
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Aug 04 '21 at 13:30
@VJD Thanks for the upvote (assuming that was you). If the answer helped, please mark it as the accepted answer. That way future visitors to the question can easily find which answer helped you resolve your question. — SchmitzIT, Aug 04 '21 at 14:25

score 3 · Accepted Answer · answered Aug 04 '21 at 13:18

You should be able to achieve your goal using something like the following:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Col1 ORDER BY Col2) AS rn FROM MyTable
)
SELECT * FROM CTE WHERE rn = 1

What it does is it creates a CTE (Common Table Expression) that adds a ROW_NUMBER on Col1, ordered by the data in row2.

In the outer select, we then only grab the rows from the CTE where the row number generated is 1.

score 2 · Answer 2 · answered Aug 04 '21 at 13:27

2

Try this

;WITH CTE(
SELECT *, 
ROW_NUMBER() OVER(PARTITIAN BY Col1 ORDER BY(SELECT NULL))RN 
FROM tblname
)
SELECT Col1, Col2, Col3, Col4 FROM CTE;

answered Aug 04 '21 at 13:27

DineshDB

5,998
7
33
49

score 1 · Answer 3 · edited Aug 05 '21 at 05:46

Depending on the flavor of SQL that you have are using, what may help you are window functions.

In SQL Server, this can be accomplished with the FIRST_VALUE window function like so:

DROP TABLE IF EXISTS #vals;

CREATE TABLE #vals (COL1 VARCHAR(10), COL2 INT, COL3 VARCHAR(5), COL4 INT);

INSERT INTO #vals (COL1, COL2, COL3, COL4)
VALUES ('asasa', 1, 'd', 44),
       ('asasa', 2, 'sd', 34),
       ('asasa', 3, 'f', 3),
       ('dssd' , 4, 'd', 2),
       ('sdsdsd', 5, 'sd', 11),
       ('dssd', 1, 'dd', 34),
       ('xxxsdsds', 2, 'd', 3),
       ('erewer', 3, 'sd', 3);

SELECT *
  FROM #vals

SELECT DISTINCT COL1, 
       FIRST_VALUE(COL2) OVER (PARTITION BY COL1 ORDER BY Col1) AS Col2,
       FIRST_VALUE(COL3) OVER (PARTITION BY COL1 ORDER BY Col1) AS Col3,
       FIRST_VALUE(COL4) OVER (PARTITION BY COL1 ORDER BY Col1) AS Col4
  FROM #vals AS v1

This returns:

|COL1       |   Col2    |   Col3    |   Col4|
|-----------|-----------|-----------|-------|
|asasa      |   1       |   d       |   44  |
|dssd       |   4       |   d       |   2   |
|erewer     |   3       |   sd      |   3   |
|sdsdsd     |   5       |   sd      |   11  |
|xxxsdsds   |   2       |   d       |   3   |

which may then be ORDERed in whatever way is needed.

score 0 · Answer 4 · answered Aug 04 '21 at 13:12

0

Select DISTINCT , should do the trick. Here is a good reference https://www.w3schools.com/sql/sql_distinct.asp

answered Aug 04 '21 at 13:12

Predrag Kisic

1
1

But how to consider only Col1 ? I have unique rows all together but duplicates in Col1. if I do distinct I am going to get my first table shown above – VJ D Aug 04 '21 at 13:13

sql selecting unique rows based on a specific column

4 Answers4