Home » SQL & PL/SQL » SQL & PL/SQL » Question about using Max
Question about using Max [message #192308] Mon, 11 September 2006 15:05 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
I know that max() will return the highest value in a column. But is it possible to use max in a query to return the entire row that the maxed column is in?


The query below will only return the max customer_id
select max(cust_id) from customer


Is it possible to get the customer name, and other columns using max?
Eg.

select max(cust_id), customer_name from customer (i tried, it doesn't work)


I don't want to use an order by statement and use rownum to get the first row.
Re: Question about using Max [message #192309 is a reply to message #192308] Mon, 11 September 2006 15:07 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
select *
  from customer
 where cust_id = (select max(cust_id) from customer);
Re: Question about using Max [message #192311 is a reply to message #192308] Mon, 11 September 2006 15:22 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
Just one more question. Is there a way in oracel to get the Top N or Bottom N of a dataset?


Thanks!
Re: Question about using Max [message #192353 is a reply to message #192311] Tue, 12 September 2006 00:50 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

 search for analytical functions


regards,
Re: Question about using Max [message #192358 is a reply to message #192353] Tue, 12 September 2006 01:05 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You must be kidding. I've done a quick search and found these threads (the first is from the FAQ pages):
TOP N
TOP N
Top N And bottom N
TOP N
TOP N
TOP N
TOP N
....

Bottom N is the similar. You just reverse the sort Wink

MHE
Re: Question about using Max [message #192359 is a reply to message #192308] Tue, 12 September 2006 01:07 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
ya top-n is quite possible.

have a look. the N = 3 in the following Example.

Actual Data:

SQL> select ename, job, sal
2 from emp;

ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
ALLEN SALESMAN 1600
WARD SALESMAN 1250
JONES MANAGER 2975
MARTIN SALESMAN 1250
BLAKE MANAGER 2850
CLARK MANAGER 2450
SCOTT ANALYST 3000
KING PRESIDENT 5000
TURNER SALESMAN 1500
ADAMS CLERK 1100
JAMES CLERK 950
FORD ANALYST 3000
MILLER CLERK 1300

14 rows selected.


Top Earners (Top to Bottom)

SQL> select ename, job, sal
2 from (select ename, job, sal from emp order by sal desc)
3 where rownum <= 3;

ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
SCOTT ANALYST 3000
FORD ANALYST 3000

Bottom Earners (Bottom to Top)

SQL> select ename, job, sal
2 from (select ename, job, sal from emp order by sal ASC)
3* where rownum <= 3
SQL> /

ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
JAMES CLERK 950
ADAMS CLERK 1100


Regards,
Liza
Re: Question about using Max [message #192364 is a reply to message #192359] Tue, 12 September 2006 01:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Liza, it seems you read the links too. Good.

MHE
Re: Question about using Max [message #192368 is a reply to message #192308] Tue, 12 September 2006 01:26 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
you put the links while i was replying the question. So i did see the links after pressing the "Submit Reply" button.
Re: Question about using Max [message #192374 is a reply to message #192368] Tue, 12 September 2006 01:37 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I was kidding, sorry I forgot to add the Very Happy. Still early on this side of the globe. I'll pour in some more coffee.

MHE
Re: Question about using Max [message #192380 is a reply to message #192308] Tue, 12 September 2006 01:48 Go to previous message
Liza79
Messages: 74
Registered: September 2006
Member
There are no offences. Have a good Coffee, and Have a nice Morning
Previous Topic: Update based on Select Query
Next Topic: Forward Declaration of Procedures and Functions in Packages
Goto Forum:
  


Current Time: Fri Dec 09 00:04:53 CST 2016

Total time taken to generate the page: 0.05460 seconds