|
Re: How to get second maximum record [message #328140 is a reply to message #328139] |
Thu, 19 June 2008 01:57   |
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   |
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 #328160 is a reply to message #328139] |
Thu, 19 June 2008 02:40   |
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 #328165 is a reply to message #328160] |
Thu, 19 June 2008 02:50   |
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   |
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 #328672 is a reply to message #328670] |
Sat, 21 June 2008 09:27  |
 |
BlackSwan
Messages: 26766 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
|
|
|