Oracle SQL select distinct

Question

I have a sample table as follows.

ID     Name            Code     Address
----+---------------+--------+----------------
1   |  Sydney Hall  |  SH    |  11 Abc Street
2   |  Sydney Hall  |  SH    |  23 Abc Street
3   |  Main Hall    |  MH    |  74 Xyz Street
4   |  Odyssey Hall |  OH    |  133 AbZ Street
5   |  Odyssey Hall |  OH    |  28 Xyx Street

I would like to select distinct Code entries as well as ID and Name for these distinct entries. For the table above I would like to get the following (so I am ignoring the building addresses).

ID     Name            Code   
----+---------------+--------+
1   |  Sydney Hall  |  SH
3   |  Main Hall    |  MH
4   |  Odyssey Hall |  OH

It's probably a Left Join but I can't seem to put it together properly (especially since I'm selecting data from the same table). Does any one have an idea about this? Thanks.

why you are not using distinct name, code then have inner join to same table — Rahul Vasantrao Kamble
– Rahul Vasantrao Kamble, Commented Feb 11, 2013 at 4:41

Nick.Mc · Accepted Answer · 2013-02-11 05:16:57Z

4

I see everyone has already answered this, but why so complicated?

SELECT 
MIN(ID) ID, 
MIN(NAME) NAME, 
CODE 
FROM TABLE 
GROUP BY CODE

answered Feb 11, 2013 at 5:16

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Meyers Over a year ago

This may show data that doesn't necessarily exist in the original data set, e.g. if MIN(ID) = 1 and MIN(NAME) shows data from row where ID = 2.

Nick.Mc Over a year ago

Based on the sample dataset it's fine. Based on real data, yes it's probably insufficient.

Eray Balkanli · Accepted Answer · 2018-02-26 15:47:49Z

2

SELECT * 
FROM [table_1] 
WHERE [ID] IN (SELECT Min([ID]) 
               FROM [table_1] 
               GROUP BY CODE
              )

edited Feb 26, 2018 at 15:47

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

answered Feb 11, 2013 at 4:20

andy

6,0792 gold badges29 silver badges50 bronze badges

1 Comment

user2014963 Over a year ago

The full code is SELECT id, name, code FROM buildings WHERE id IN (SELECT MIN(id) FROM tb_buildings GROUP BY Code)

Mike Meyers · Accepted Answer · 2013-02-11 05:16:59Z

2

There are two ways that I would look at doing this. One is to use the FIRST aggregate function (documented here). The syntax is a little confusing but it should do the job

Select
  MIN(ID) keep (dense_rank first order by id) as id,
  MIN(NAME) keep (dense_rank first order by id) as name,
  CODE
FROM YOUR_TABLE
GROUP BY CODE

The other alternative method that I would suggest is using the ROW_NUMBER function as suggested by @techdo, although I think you would need to remove the NAME column from that answer and instead use:

SELECT * FROM(
  SELECT 
      ROW_NUMBER() over (partition by CODE order by ID) RNUM, 
      ID, 
      NAME, 
      CODE
    FROM YOUR_TABLE
  )x where RNUM=1;

answered Feb 11, 2013 at 5:16

Mike Meyers

2,8951 gold badge20 silver badges26 bronze badges

Comments

Eray Balkanli · Accepted Answer · 2018-02-26 15:47:02Z

0

You can use this one also:

SELECT ID, Name, Code  
FROM table 
WHERE ID IN (SELECT Max(ID) 
             FROM table 
             GROUP BY Code
            )

edited Feb 26, 2018 at 15:47

Eray Balkanli

8,05013 gold badges60 silver badges94 bronze badges

answered Feb 11, 2013 at 4:51

user2001117

3,7871 gold badge20 silver badges18 bronze badges

Comments

TechDo · Accepted Answer · 2013-02-11 04:29:42Z

-1

Please try:

SELECT * FROM(
  SELECT 
      ROW_NUMBER() over (partition by NAME, CODE order by NAME, CODE) RNUM, 
      ID, 
      NAME, 
      CODE, 
      ADDRESS 
    FROM YourTABLE
  )x where RNUM=1;

answered Feb 11, 2013 at 4:29

TechDo

18.7k3 gold badges55 silver badges64 bronze badges

Collectives™ on Stack Overflow

Oracle SQL select distinct

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related