Geek asks and answers

Oracle SQL highlights various

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 individual code entries as well as an identifier and a name for these different entries. For the table above, I would like to get the following (so I ignore the addresses of the building).

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

This is probably Left Join, but I cannot assemble it correctly (especially since I select data from a single table). Does anyone have any ideas about this? Thank.

+5
source share
5 answers
SELECT * 
FROM [table_1] 
WHERE [ID] IN (SELECT Min([ID]) 
               FROM [table_1] 
               GROUP BY CODE
              )
+2
source

I see that everyone has already answered this, but why is it so difficult?

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

, . FIRST ( ). ,

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

Another alternative method that I would suggest is using the function ROW_NUMBERsuggested by @techdo, although I think you will need to remove the NAME column from this answer and use instead:

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

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;
0
source

You can also use this:

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

More articles:


All Articles