Home » SQL & PL/SQL » SQL & PL/SQL » MAX function
MAX function [message #307024] Mon, 17 March 2008 10:38 Go to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
I wish to write a query as below:

select max(id), name from emp where name like '%TOM%';


However, this returns more than one record.

I want it return the max id along with the name.

However, here it returns all records where name like '%TOM%'.

Could someone help me out here?

Thanks,

Sharath
Re: MAX function [message #307025 is a reply to message #307024] Mon, 17 March 2008 10:43 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That should throw a

ORA-00937: not a single-group group function


anyway. You need to add a "group by [column_name]" clause at the end.
Re: MAX function [message #307026 is a reply to message #307024] Mon, 17 March 2008 10:47 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
I guess the OP wants the name of the one with the MAX(id).

As far as I know that can't be done in a simple query; a sub-query is at least required.

Re: MAX function [message #307027 is a reply to message #307026] Mon, 17 March 2008 10:49 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Ah, yes. You are right.

So the inner query would be

select max(id) maxid, name from emp where name like '%TOM%' group by name;


THAT you can order by maxid descending, and then return the first row in an outer query.
Re: MAX function [message #307039 is a reply to message #307024] Mon, 17 March 2008 12:09 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Firstly, as others have mentioned you need a group by clause.
Also, you don't necessarily have to do it in a subquery. Check this link.

http://www.orafaq.com/forum/t/98933/94420/

Having said that I am not sure which version is performant (@Rleishman's) or (@Michael's). It's worth a try.

Regards

Raj
Re: MAX function [message #307043 is a reply to message #307039] Mon, 17 March 2008 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It depends on things like row number, indexes, value distribution but in most cases it is the same.

Regards
Michel
Re: MAX function [message #307129 is a reply to message #307039] Mon, 17 March 2008 22:41 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Rajaram

I tried to implement the dense rank solution but my query still returns two rows. Please correct me if this query is wrong...

select name, max(id) KEEP (DENSE_RANK LAST ORDER BY id)
from emp 
where name like '%TOM%';


Is the above query right?

It still returns me two records...

Thanks,

Sharath

[Updated on: Mon, 17 March 2008 22:41]

Report message to a moderator

Re: MAX function [message #307131 is a reply to message #307027] Mon, 17 March 2008 22:47 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
ThomasG,

With reference to your post below,

Could you please tell me how to return the first query of the result set?

select * from (select max(id) maxid, name from emp where name like '%TOM%' group by name) order by maxid desc;

The above query will return the all the rows in descending order.

How do I return the first row?

Thanks,

Sharath
Re: MAX function [message #307143 is a reply to message #307131] Mon, 17 March 2008 23:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT empno, ename
  2  FROM   emp
  3  WHERE  ename LIKE '%MI%'
  4  /

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7934 MILLER

SCOTT@orcl_11g> SELECT empno, ename
  2  FROM   (SELECT empno, ename
  3  	     FROM   emp
  4  	     WHERE  ename LIKE '%MI%'
  5  	     ORDER  BY empno DESC)
  6  WHERE  ROWNUM = 1
  7  /

     EMPNO ENAME
---------- ----------
      7934 MILLER

SCOTT@orcl_11g> SELECT empno, ename
  2  FROM   emp
  3  WHERE  empno =
  4  	    (SELECT MAX (empno)
  5  	     FROM   emp
  6  	     WHERE  ename LIKE '%MI%')
  7  /

     EMPNO ENAME
---------- ----------
      7934 MILLER

SCOTT@orcl_11g> SELECT MAX (empno) empno,
  2  	    MAX (ename) KEEP (DENSE_RANK LAST ORDER BY empno) ename
  3  FROM   emp
  4  WHERE  ename LIKE '%MI%'
  5  /

     EMPNO ENAME
---------- ----------
      7934 MILLER

SCOTT@orcl_11g> 

[Updated on: Mon, 17 March 2008 23:58]

Report message to a moderator

Re: MAX function [message #307148 is a reply to message #307143] Tue, 18 March 2008 00:21 Go to previous messageGo to next message
hedonist123
Messages: 119
Registered: August 2007
Senior Member
Hi Barbara/Thomas/Michel/Rajaram/Marc,

Thank you so much for your detailed inputs.

Huge help!!

Sharath
Re: MAX function [message #307654 is a reply to message #307024] Wed, 19 March 2008 06:29 Go to previous messageGo to next message
smrutiranjan
Messages: 3
Registered: March 2008
Location: mumbai
Junior Member
Try this

select max(id),ename
from emp
group by ename;
Re: MAX function [message #307664 is a reply to message #307654] Wed, 19 March 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This has already been given.
Please read the whole topic before answering.

Regards
Michel
Re: MAX function [message #307734 is a reply to message #307024] Wed, 19 March 2008 14:06 Go to previous message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
select id,name
from emp
where id = (select max(id) from emp where name like '%TOM%');



Sorry barbara, I just noticed your post....

[Updated on: Wed, 19 March 2008 14:08]

Report message to a moderator

Previous Topic: finding latest date in oracle
Next Topic: Need detail records but also need sum in same query
Goto Forum:
  


Current Time: Sun Dec 04 22:36:12 CST 2016

Total time taken to generate the page: 0.11828 seconds