Home » SQL & PL/SQL » SQL & PL/SQL » How to get second maximum record (Oracle 9i)
How to get second maximum record [message #328139] Thu, 19 June 2008 01:47 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Frineds,

How to select second maximum record? means, previous record of last inserted record.

Re: How to get second maximum record [message #328140 is a reply to message #328139] Thu, 19 June 2008 01:57 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd use the Row_Number or Dense_Rank analytic functions to put an order to the rows, and then select the one(s) with a value of 2.

previous record of last inserted record

If you're ordering the rows by a column thhat's populated on insert by a sequence or by sysdate then this will work.
If you're hoping for some way of finding the last but one row you inserted without a column like that then you're probably out of luck
Re: How to get second maximum record [message #328151 is a reply to message #328139] Thu, 19 June 2008 02:34 Go to previous messageGo to next message
tejas_gandhi
Messages: 8
Registered: August 2007
Junior Member
Try following code
select a.* from 
 (select s.*,max(rn) over() maxr from 
   (select TAB1.*,rownum rn from TAB1 order by rownum desc) s
 ) a where maxr-1=rn;


Replace TAB1 with table name for which you want last but one record.

This query works, but i guess there could be better way to do it.
Re: How to get second maximum record [message #328153 is a reply to message #328151] Thu, 19 June 2008 02:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
tejas_gandhi wrote on Thu, 19 June 2008 09:34
Try following code
select a.* from 
 (select s.*,max(rn) over() maxr from 
   (select TAB1.*,rownum rn from TAB1 order by rownum desc) s
 ) a where maxr-1=rn;


Replace TAB1 with table name for which you want last but one record.

This query works, but i guess there could be better way to do it.

There is and JRowbottom already gave it.

Regards
Michel

Re: How to get second maximum record [message #328160 is a reply to message #328139] Thu, 19 June 2008 02:40 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Preety confused with your subject line! May be you are searching for this :

Based on your statement:
Quote:

previous record of last inserted record



SQL> CREATE TABLE EMP(EMPID VARCHAR2(3), SALARY NUMBER(8,2));

Table created.

SQL> INSERT INTO EMP VALUES ('&A',&B);
Enter value for a: 1
Enter value for b: 70000
old   1: INSERT INTO EMP VALUES ('&A',&B)
new   1: INSERT INTO EMP VALUES ('1',70000)

1 row created.

SQL> /
Enter value for a: 2
Enter value for b: 80000
old   1: INSERT INTO EMP VALUES ('&A',&B)
new   1: INSERT INTO EMP VALUES ('2',80000)

1 row created.

SQL> /
Enter value for a: 5
Enter value for b: 56765
old   1: INSERT INTO EMP VALUES ('&A',&B)
new   1: INSERT INTO EMP VALUES ('5',56765)

1 row created.

SQL> /
Enter value for a: 4
Enter value for b: 42343
old   1: INSERT INTO EMP VALUES ('&A',&B)
new   1: INSERT INTO EMP VALUES ('4',42343)

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM EMP;

EMP     SALARY
--- ----------
1        70000
2        80000
5        56765
4        42343

[b]
(SELECT * FROM (SELECT (ROWNUM) RN, EMPID,SALARY FROM EMP)
		   WHERE RN >( SELECT MAX(ROWNUM)-2 FROM EMP)) 
  MINUS
(SELECT * FROM (SELECT (ROWNUM) RN,EMPID,SALARY FROM EMP)
		   WHERE RN >( SELECT MAX(ROWNUM)-1 FROM EMP));
[/b]

        RN EMP     SALARY
---------- --- ----------
         3 5        56765









Regards,
Oli

[Updated on: Thu, 19 June 2008 02:41]

Report message to a moderator

Re: How to get second maximum record [message #328162 is a reply to message #328160] Thu, 19 June 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is wrong.
Read carefully JRowbottom's post.

Regards
Michel
Re: How to get second maximum record [message #328165 is a reply to message #328160] Thu, 19 June 2008 02:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
No no no no no.

Fundamental principle 1) If you do not use an ORDER BY clause in your SQL then there is no guaranteed order to the rows you get back .

Fundamental principle 2) Rownum just applies an increasing number to each row in the order it is retrieved.

For small tables where only inserts are performed, it looks like rows are always returned in the order that they are inserted into the database, but this is a special case. In general, if you don't put an ORDER BY clause in, you should assume the rows can come back in any order Oracle chooses.

If you don't have a column or columns in the table that allow you to order the rows in a way that matches your requiremnt, then you can't get the top-n rows (and indeed without a way to order the rows, the concept of a top-n is meaningless)
Re: How to get second maximum record [message #328166 is a reply to message #328165] Thu, 19 June 2008 03:03 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
JRowbottom wrote on Thu, 19 June 2008 02:50
No no no no no.

Fundamental principle 1) If you do not use an ORDER BY clause in your SQL then there is no guaranteed order to the rows you get back .

Fundamental principle 2) Rownum just applies an increasing number to each row in the order it is retrieved.

For small tables where only inserts are performed, it looks like rows are always returned in the order that they are inserted into the database, but this is a special case. In general, if you don't put an ORDER BY clause in, you should assume the rows can come back in any order Oracle chooses.

If you don't have a column or columns in the table that allow you to order the rows in a way that matches your requiremnt, then you can't get the top-n rows (and indeed without a way to order the rows, the concept of a top-n is meaningless)


@Jrowbottom/Michel
Thanks for for the explanation and pointing out my mistake.

Regards,
Oli
Re: How to get second maximum record [message #328646 is a reply to message #328166] Sat, 21 June 2008 02:22 Go to previous messageGo to next message
hamidmahmood
Messages: 30
Registered: August 2006
Member

SELECT ename, sal
FROM ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) sal_rank
FROM emp )
WHERE sal_rank = 2;
Re: How to get second maximum record [message #328651 is a reply to message #328646] Sat, 21 June 2008 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What does this add to the previous post but to show us you are unable to format your post?

please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel
Re: How to get second maximum record [message #328670 is a reply to message #328139] Sat, 21 June 2008 09:20 Go to previous messageGo to next message
annupamkumar
Messages: 1
Registered: June 2008
Junior Member
can try this one

select * from emp h where 2=(select count(distinct sal) from emp where h.sal <= sal);
Re: How to get second maximum record [message #328672 is a reply to message #328670] Sat, 21 June 2008 09:27 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
How does below
annupamkumar wrote on Sat, 21 June 2008 07:20
can try this one

select * from emp h where 2=(select count(distinct sal) from emp where h.sal <= sal);

satisfy this requirement? "means, previous record of last inserted record."
JRowbottom gave correct answer above/previously.
Records can be inserted in any order with regards to actual salary values.

[Updated on: Sat, 21 June 2008 09:28] by Moderator

Report message to a moderator

Previous Topic: Alter table
Next Topic: RUNSTATS Package Error
Goto Forum:
  


Current Time: Fri Dec 09 21:36:03 CST 2016

Total time taken to generate the page: 0.14520 seconds