OraFAQ Forum: SQL & PL/SQL » Select a row with a max value

Home » SQL & PL/SQL » SQL & PL/SQL » Select a row with a max value

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select a row with a max value [message #383708]

Thu, 29 January 2009 18:37

tmcallister
Messages: 107
Registered: December 2007

Senior Member

Suppose I have some trivial data that looks like

create table temp
(
    id  varchar2(16),
    col1  varchar2(16),
    col2 varchar2(16)
);

insert into temp values ('A','1','this');
insert into temp values ('A','2','is');
insert into temp values ('A','3','an');
insert into temp values ('A','4','example');
insert into temp values ('A','5','file');
insert into temp values ('A','6','with');
insert into temp values ('A','7','8');
insert into temp values ('A','8','lines');
insert into temp values ('B','1','this');
insert into temp values ('B','2','is');
insert into temp values ('B','3','an');
insert into temp values ('B','4','example');
insert into temp values ('B','5','with');
insert into temp values ('B','6','less');

I would like a query that selects only the rows with the maximum value of col1, so the result would be:

ID	col1	col2
A	8	lines
B	6	less

What I have is

SELECT ID, col1, col2
  FROM (SELECT ID, col1, col2,
               ROW_NUMBER () OVER (PARTITION BY ID ORDER BY col1 ASC) inc,
               COUNT (*) OVER (PARTITION BY ID) cnt
          FROM temp)
 WHERE inc = cnt

But I'm certain that I'm making this wayyyy more complex than it needs to be. Is there an easier way?

Report message to a moderator

Re: Select a row with a max value [message #383716 is a reply to message #383708]

Thu, 29 January 2009 19:35

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

I don't have access to a DB right not to test it, but this should also work, and has one less analytic function:

select ID, col1, col2
 from (
   SELECT ID, col1, col2,
          MAX(col1) over OVER (PARTITION BY ID) max1
          FROM temp
  )
WHERE col1 = max1

Report message to a moderator

Re: Select a row with a max value [message #383765 is a reply to message #383708]

Fri, 30 January 2009 00:41

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

SQL> with 
  2    data as (
  3      select id, col1, col2,
  4             rank() over (partition by id order by col1 desc) rk
  5      from temp
  6    )
  7  select id, col1, col2
  8  from data
  9  where rk = 1
 10  /
ID               COL1             COL2
---------------- ---------------- ----------------
A                8                lines
B                6                less

2 rows selected.

Regards
Michel

Report message to a moderator

Re: Select a row with a max value [message #383787 is a reply to message #383765]

Fri, 30 January 2009 03:50

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

I'll see your 1 analytic function, and raise you no nested queries and an aggregate function.

select id
      ,max(col1) 
      ,max(col2) keep (dense_rank last order by col1 asc)
from   temp
group by id;

Report message to a moderator

Re: Select a row with a max value [message #383837 is a reply to message #383708]

Fri, 30 January 2009 10:48

tmcallister
Messages: 107
Registered: December 2007

Senior Member

Thanks JRowbottom, that one seems the fastest/simplest to me, and is sufficient for my needs, and adds another tools to my sqlbox (time to go read up on dense_rank).

However as a curiosity...

Michel there is a slight nuance with yours where it if there is a duplicate col1 value it will select both of them, rather than choosing one arbitrarily which the other method do.

To see this add the following to the initial sample

insert into temp values ('A','8','zlines');

For that nuance it is possible to alter my original to short by a second column (and similarly for Michel's).

SELECT ID, col1, col2
  FROM (SELECT ID, col1, col2,
               ROW_NUMBER () OVER (PARTITION BY ID ORDER BY col1 asc, col2 desc) inc,
               COUNT (*) OVER (PARTITION BY ID) cnt
          FROM temp)
 WHERE inc = cnt

Which yields

ID	COL1	COL2
A	8	lines
B	6	less

with asc/desc

and

ID	COL1	COL2
A	8	zlines
B	6	less

with asc/asc

I'm not certain it's possible to do this (specifically the asc/desc sort, asc/asc is easy enough) with your method JRowbottom.

[Updated on: Fri, 30 January 2009 10:56]

Report message to a moderator

Re: Select a row with a max value [message #383838 is a reply to message #383837]

Fri, 30 January 2009 10:58

sachinjainonweb
Messages: 24
Registered: October 2005

Junior Member

select *
from temp
where (id,col1) in (select id,max(col1)
                    from temp
                    group by id);

Report message to a moderator

Re: Select a row with a max value [message #383840 is a reply to message #383837]

Fri, 30 January 2009 11:04

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Michel there is a slight nuance with yours where it if there is a duplicate col1 value it will select both of them, rather than choosing one arbitrarily which the other method do.

Your requirement was:

Quote:

I would like a query that selects only the rows with the maximum value of col1,

This implies ALL rows with the maximum value, so I used RANK, if you want ONE row, use ROW_NUMBER instead.

Regards
Michel

Report message to a moderator

Previous Topic:	Dynamic display of columns in Pivot & Crosstab [merged 3]
Next Topic:	Data load from Excel to Oracle 10g (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 18 18:07:34 CDT 2025