Oracle Sql : distinct value in a specific field

Question

I have the following table :

  **Country     Name        Number**
   us           John         45
   us           Jeff         35
   fr           Jean         31
   it           Luigi        25
   fr           Maxime       23
   ca           Justin       23

This table is order by Number. I want to have a query that for each country give me the name with highest number :

  **Country     Name        Number**
   us           John         45
   fr           Jean         31
   it           Luigi         25
   ca           Justin        23

I try to use distinct but I can't only make it on country if I want to print the all thing...

Have an idea ?'

EDIT : The table is obtain by a subquery

select country, max(number) from table group by country – René Winkler May 06 '15 at 19:50 — René Winkler, May 06 '15 at 19:50
@RenéWinkler He also wants the name in the results – Troy May 06 '15 at 20:02 — Troy, May 06 '15 at 20:02

Rajesh · Answer 1 · 2015-05-06T20:27:23.667

1

Try below query:

SELECT Country, MAX(numbeer) FROM Table_Name GROUP BY Country

PFB updated query to include Name:

SELECT t1.* FROM table1 t1 INNER JOIN 
(SELECT country, max(numbeer) as numbeer FROM table1 GROUP BY country) t2
ON t1.country=t2.country AND t1.numbeer=t2.numbeer;

edited May 06 '15 at 20:27

answered May 06 '15 at 19:54

Rajesh

2,135
1
12
14

He also wants the name. – Troy May 06 '15 at 19:59
@Danny I have updated query to include Name column as asked. You need to replace table1 with actual name. – Rajesh May 06 '15 at 20:29

score 1 · Answer 2 · answered May 06 '15 at 20:14

I do not have an Oracle db handy but I got this working in my SQL Server db and am pretty sure it will work in Oracle (meaning I think I am using ANSI sql which should work in most db's):

SELECT m.Country,m.Name,m.number
FROM mytable m
INNER JOIN (
    select country, MAX(number) as number 
    FROM mytable GROUP BY Country
) AS tmp ON m.Country = tmp.Country and m.Number = tmp.number
ORDER BY m.Number DESC

This has the added benefit that it should give you records when you have two people in a given country that have the same number.

You didn't give us a table name so I just called it mytable.

Thanks for the answer ! Oracle SQL tells me that I have a 'missing keyword' but I can't figure out which one.. — Danny, May 06 '15 at 20:31

MT0 · Accepted Answer · 2015-05-06T20:49:12.493

1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Countries AS
          SELECT 'us' AS Country, 'John' AS Name, 45 AS "Number" FROM DUAL
UNION ALL SELECT 'us' AS Country, 'Jeff' AS Name, 35 AS "Number" FROM DUAL
UNION ALL SELECT 'fr' AS Country, 'Jean' AS Name, 31 AS "Number" FROM DUAL
UNION ALL SELECT 'it' AS Country, 'Luigi' AS Name, 25 AS "Number" FROM DUAL
UNION ALL SELECT 'fr' AS Country, 'Maxime' AS Name, 23 AS "Number" FROM DUAL
UNION ALL SELECT 'ca' AS Country, 'Justin' AS Name, 23 AS "Number" FROM DUAL;

Query 1:

SELECT Country,
       MAX( Name ) KEEP ( DENSE_RANK FIRST ORDER BY "Number" DESC ) AS "Name",
       MAX( "Number" ) AS "Number"
FROM   Countries
GROUP BY Country

Results:

| COUNTRY |   Name | Number |
|---------|--------|--------|
|      ca | Justin |     23 |
|      fr |   Jean |     31 |
|      it |  Luigi |     25 |
|      us |   John |     45 |

edited May 06 '15 at 20:49

answered May 06 '15 at 20:38

MT0

143,790
11
59
117

Perfect ! It works great ! Can you explain the dense_rank stuff please ? And if I want to print also the number, how can I do ? – Danny May 06 '15 at 20:43
@Danny - See my answer here: http://stackoverflow.com/a/20157109/1509264 – MT0 May 06 '15 at 20:47
@Danny Edited to show number. – MT0 May 06 '15 at 20:50
Nothing to say, you're the best ! – Danny May 06 '15 at 20:52

score 0 · Answer 4 · answered May 06 '15 at 19:52

0

Use row_number():

select t.Country, t.Name, t.Number
from (select t.*,
             row_number() over (partition by country order by number desc) as seqnum
      from table t
     ) t
where seqnum = 1;

answered May 06 '15 at 19:52

Gordon Linoff

1,242,037
58
646
786

It seems I can't use seqnum outside of the nested query... – Danny May 06 '15 at 20:18
@Danny . . . Of course you can. Are you sure this is the query that you are running? – Gordon Linoff May 06 '15 at 21:10

Oracle Sql : distinct value in a specific field

4 Answers4