sql selecting unique rows based on a specific column

user_13566273 0
Viewed 55

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.

sql-server sql
4 Answers

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.

user_5697110

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;
user_38765650

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.

user_143298300
user_133497460
Related