How to select rows based on unique column

Question

Hi,

I have table with values as

    ColumnA   ColumnB

    ASD       A
    CSD       B
    DSD       C
    ESD       D
    FSD       D
    GSD       D

Column A is primary key, I need result as

    ColumnA   ColumnB

    ASD       A
    CSD       B
    DSD       C
    ESD       D

I want all unique values from column B & their associated first values columnA.

I tried few queries & looked on Google but couldn't find ant solution .

Thanks, Rohit.

Answer 1

First is First in key order (i.e. alphabetically).

SELECT MIN(ColumnA) AS ColumnA, ColumnB
FROM tbl
GROUP BY ColumnB

You've tagged both MySQL and Oracle for some reason. The above will work in both.

Oracle also has analytical functions that can help with this general type of query.

Answer 2

select min(columna) columna, columnb
from mytable
group by columnb;

Answer 3

With analytics (Oracle):

SQL> WITH tab AS (
  2     SELECT 'ASD' columnA, 'A' columnB FROM DUAL
  3     UNION ALL SELECT 'CSD', 'B' FROM DUAL
  4     UNION ALL SELECT 'DSD', 'C' FROM DUAL
  5     UNION ALL SELECT 'ESD', 'D' FROM DUAL
  6     UNION ALL SELECT 'FSD', 'D' FROM DUAL
  7     UNION ALL SELECT 'GSD', 'D' FROM DUAL
  8  )
  9  SELECT columnA, columnB
 10    FROM (SELECT columnA, columnB,
 11                 rank() over(PARTITION BY columnB ORDER BY columnA) rnk
 12            FROM tab)
 13   WHERE rnk = 1;

COLUMNA COLUMNB
------- -------
ASD     A
CSD     B
DSD     C
ESD     D

Answer 4

select min(ColumnA) as ColumnA,ColumnB from table
group by ColumnB

Answer 5

CREATE TABLE #t_Val
(
    ColumnA VARCHAR(3) PRIMARY KEY
,   ColumnB CHAR(1) NOT NULL
)

INSERT #t_Val
SELECT 'ASD' ,  'A'
UNION
SELECT 'CSD' ,  'B'
UNION
SELECT 'DSD' ,  'C'
UNION
SELECT 'ESD' ,  'D'
UNION
SELECT 'FSD' ,  'D'
UNION
SELECT 'GSD' ,  'D'


SELECT  MIN(ColumnA),ColumnB
FROM #t_Val
GROUP BY ColumnB

Answer 6

Just another way of doing the same thing:

SELECT DISTINCT
    FIRST_VALUE(ColumnA)
    OVER (PARTITION BY ColumnB
          ORDER BY ColumnA) AS ColumnA,
    ColumnB
FROM tbl

This has the advantage of being usable with more than one column (i.e. where the GROUP BY method doesn't work).