CODEWITHSUNDEEP
sqlmysqloracle-database
Rohit Desai
Rohit Desai

Reputation: 1

How to select rows based on unique column

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.

Upvotes: 0

Views: 2394

Answers (7)

G-Rajendra
G-Rajendra

Reputation: 215

Here is solution

CREATE TABLE tbl_test ( col1 varchar(5) collate latin1_general_ci NOT NULL, col2 varchar(5) collate latin1_general_ci NOT NULL, PRIMARY KEY (col1) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO tbl_test VALUES ('ASD', 'A'); INSERT INTO tbl_test VALUES ('CSD', 'B'); INSERT INTO tbl_test VALUES ('DSD', 'C'); INSERT INTO tbl_test VALUES ('ESD', 'D'); INSERT INTO tbl_test VALUES ('FSD', 'D'); INSERT INTO tbl_test VALUES ('GSD', 'D');

SELECT DISTINCT ( col2 ), col1 FROM tbl_test GROUP BY col2 LIMIT 0 , 30;

Upvotes: 0

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

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).

Upvotes: 0

greektreat
greektreat

Reputation: 2561

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

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

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

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67762

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

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132670

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453707

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.

Upvotes: 3

Related Questions