CODEWITHSUNDEEP
sqldistinct
user1397624
user1397624

Reputation: 9

Need help for the SQL Distinct

Original table

+-- Col 1 --+-- Col 2 --+
+--   1   --+--   a   --+
+--   1   --+--   b   --+
+--   1   --+--   c   --+
+--   2   --+--   d   --+
+--   3   --+--   e   --+
+--   3   --+--   f   --+
+-----------------------+

Result : 

+-- Col 1 --+-- Col 2 --+
+--   1   --+--   a   --+
+--   2   --+--   d   --+
+--   3   --+--   e   --+
+-----------------------+

Any idea how to distinct the table?

Upvotes: 0

Views: 113

Answers (4)

Tyler Nguyen
Tyler Nguyen

Reputation: 537

If you use SQL Server, with table name is TEST, you can use CROSS APPLY in T-SQL as below:

SELECT DISTINCT Col1, TEST2.Col2 FROM TEST
CROSS APPLY (SELECT TOP 1 Col2 FROM TEST TEST2
        WHERE TEST2.Col1 = TEST.Col1) TEST2

Upvotes: 0

James Anderson
James Anderson

Reputation: 27478

"DISTINCT" applies to every column in the result set.

A "GROUP BY" will provide the result you require, but you need to think about whether "col2" has any real meaning in your result set. 

 SELECT col1, min(col2) as col2
      FROM T
     GROUP BY col1

Upvotes: 1

ravi
ravi

Reputation: 3424

You don't have same value in col2 for same values in col1, so result could not be generated by DISTINCT if you are selecting both columns.

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109603

SELECT t.Col1, t.Col2
FROM T t
WHERE NOT EXISTS(SELECT *
                 FROM T s
                 WHERE t.Col1 = s.Col1 AND t.Col2 > s.Col1)

Upvotes: 0

Related Questions