Home » SQL & PL/SQL » SQL & PL/SQL » Select a row with a max value
Select a row with a max value [message #383708] Thu, 29 January 2009 18:37 Go to next message
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?


Re: Select a row with a max value [message #383716 is a reply to message #383708] Thu, 29 January 2009 19:35 Go to previous messageGo to next message
ThomasG
Messages: 3189
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
Re: Select a row with a max value [message #383765 is a reply to message #383708] Fri, 30 January 2009 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Select a row with a max value [message #383787 is a reply to message #383765] Fri, 30 January 2009 03:50 Go to previous messageGo to next message
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;

Re: Select a row with a max value [message #383837 is a reply to message #383708] Fri, 30 January 2009 10:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
sachinjainonweb
Messages: 24
Registered: October 2005
Junior Member
select *
from temp
where (id,col1) in (select id,max(col1)
                    from temp
                    group by id);
Re: Select a row with a max value [message #383840 is a reply to message #383837] Fri, 30 January 2009 11:04 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Dynamic display of columns in Pivot & Crosstab [merged 3]
Next Topic: Data load from Excel to Oracle 10g (merged)
Goto Forum:
  


Current Time: Sat Dec 03 01:18:31 CST 2016

Total time taken to generate the page: 0.10402 seconds