Get last record of result set

Question

Following is the sql query (oracle) that I use to get the last record of the returning result of the select query. Is this the optimum way?

SELECT HAZMAT_PLACARD_NOTATION
        INTO v_pcn
        FROM HAZMAT_CLASS_IRF
        WHERE HAZMAT_CD = p_stcc_cd and ROWID = (SELECT MAX(ROWID) FROM HAZMAT_CLASS_IRF WHERE HAZMAT_CD = p_stcc_cd);

Following is an example result set of the select query where HAZMAT_CD = 4920111 But the last row with the HAZMAT_PLAYCARD_NOTATION value SPONTANEOUSLY COMBUSTIBLE should be retrieved. That's the objective

enter image description here

I think you can use `SELECT TOP 1` and `ORDER BY ROWID DESC` - But I'm not sure in Oracle ;). — shA.t, Jul 15 '15 at 04:34
I don't think you can use `ORDER BY` here. This query seems OK to me. — Tim Biegeleisen, Jul 15 '15 at 04:34
Beware of `ROWID`, its order in no way indicates the order of insertion (see: http://www.dba-oracle.com/t_oracle_find_last_row.htm) — acesargl, Jul 15 '15 at 12:02
So what's the solution if there's no column available to determine the latest entry of a result set? — Asiri Liyana Arachchi, Jul 15 '15 at 18:05

score 2 · Answer 1 · edited May 23 '17 at 11:58

2

Get last record of result set

There are many ways:

Oracle Pre-12c version:

ROWNUM in sub-query and ORDER BY in outer query
Analytic function

Oracle 12c version:

Top-n Row Limiting feature

Using ROWNUM

SELECT HAZMAT_PLACARD_NOTATION
INTO v_pcn
FROM
  (SELECT HAZMAT_PLACARD_NOTATION,
    ROWNUM rn
  FROM HAZMAT_CLASS_IRF
  WHERE HAZMAT_CD = p_stcc_cd
  ORDER BY        <sort_column> DESC
  )
WHERE rn =1;

Using Top-n Row Limiting feature

SELECT HAZMAT_PLACARD_NOTATION
 INTO v_pcn
  FROM HAZMAT_CLASS_IRF
  WHERE HAZMAT_CD = p_stcc_cd
  ORDER BY        <sort_column> DESC
FETCH FIRST 1 ROW ONLY;

Have a look at this answer for examples and detailed explanation.

edited May 23 '17 at 11:58

Community

1
1

answered Jul 15 '15 at 05:00

Lalit Kumar B

47,486
13
97
124

This is a good explanation. But what's the advantage using the query with ROWNUM you've provided over the query in my question. Is that the optimum way? – Asiri Liyana Arachchi Jul 15 '15 at 05:54
@AsiriLiyanaArachchi Using **ROWNUM** you scan the table only once, and apply ROWNUM on the result set of the sub-query . In your query, you are scanning the table twice, which is not optimal. – Lalit Kumar B Jul 15 '15 at 06:20
@AsiriLiyanaArachchi Please mark it as answered, would help others. – Lalit Kumar B Jul 15 '15 at 06:21
I've edited the question. I can't use order by . Given that the query I've mentioned is the only way I could come up with to get the last row. Any idea? – Asiri Liyana Arachchi Jul 15 '15 at 09:05
I've noticed that in your solution with ROWNUM there may be a mistake based on the question, what we want is the last record of a result set, so by doing the later filtering "WHERE rn =1" you are getting the row with the rownum value of 1 regardless of ordering in the sub-query. In this case using WHERE ROWNUM = 1 would give the wanted result. – Hyque Oct 27 '21 at 19:37

score 0 · Answer 2 · answered Jul 15 '15 at 04:38

0

In mysql ,you can try this.

SELECT * 
FROM  `table` 
ORDER BY id DESC 
LIMIT 0 , 1

answered Jul 15 '15 at 04:38

The OP wants to do an `INSERT INTO` which makes the question a bit trickier than the query you have posted. – Tim Biegeleisen Jul 15 '15 at 04:38
you question is "Get last record of result set" – Jul 15 '15 at 04:40
This is not mysql and it's a part of stored procedure. I'm saving returning value to the variable v_pcn – Asiri Liyana Arachchi Jul 15 '15 at 04:44
There is no `LIMIT 0,1` in Oracle – Jul 15 '15 at 11:19
Yes,this is Mysql Query – Jul 15 '15 at 11:38

score 0 · Answer 3 · edited Jul 15 '15 at 11:13

0

select * from
   ( 
     select * from table order by id desc
   )
where rownum = 1

edited Jul 15 '15 at 11:13

Glorfindel

21,988
13
81
109

answered Jul 15 '15 at 04:41

lookslikeanevo

566
1
5
14

I don't this this works. Doesn't this return the first row of the result set ? – Asiri Liyana Arachchi Jul 15 '15 at 04:44
1

it does , but its also why im sorting by whatever ID you need to sort by desc – lookslikeanevo Jul 15 '15 at 04:46
Isn't order by executed after rownum? http://stackoverflow.com/questions/15091849/how-to-use-oracle-order-by-and-rownum-correctly – Asiri Liyana Arachchi Jul 15 '15 at 04:48

score 0 · Answer 4 · answered Sep 27 '18 at 13:48

In older sqlserver versions (<2014) you don't have the ROW_NUMBER at your disposal.

But you can easily use Top 1 and order by desc

select top 1 * from tblc2scdocumentcodes where C2scDocumentCode like '%.14.%' and C2scDocumentCode like '%DTP.SO.%' order by C2scDocumentCode desc

The query returns a resultset. The order of this resultset is 'reversed' and then limited to 1 record

Get last record of result set

4 Answers4

Linked