General
cancel
Showing results for 
Search instead for 
Did you mean: 

only retrive LAST rown of sql query

SOLVED
Go to solution
Ian McClement_1
Occasional Advisor

only retrive LAST rown of sql query

Hi
Can someone tell me how to only retrieve the LAST row of an sql query (equivalent of unix "tail -1 " command)
I have tried reversinq the order by clause and using "rownum = 1" but the ronwums are allocated before the order by

Any help much appreciated

Cheers
Ian
7 REPLIES
Tom Geudens
Honored Contributor

Re: only retrive LAST rown of sql query

Hi,
It's been a while, but something like
select field01, field02, field03
from your_table
where field01 = (select max(field01)
from your_table);
might work since you do seem to have fields you can order the table by.

Hope this helps,
Tom Geudens
A life ? Cool ! Where can I download one of those from ?
Steve Lewis
Honored Contributor

Re: only retrive LAST rown of sql query

You definitely need an order by, otherwise the order returned is undefined, depending on whether any rows are cached in buffers.

If your query was something like:
select name, a_number
from mytab
order by a_number

and you want the last row returned from above, then you can change it to

select first 1 name, a_number
from mytab
order by a_number desc

This is informix syntax, don't know about oracle/sybase etc.

Otherwise you can do it the hard way (i.e. write a stored procedure or program to unload the rows, tail -1 on the file and read it back in returning one row)
Graham Cameron_1
Honored Contributor
Solution

Re: only retrive LAST rown of sql query

Ian

If you are talking Oracle...
As Steve says, without an 'order by' statement the order in which Oracle will retrieve your rows is indeterminate and may not be consistent between successive invocations of the same query.
If you *do* use 'order by' you can combine it
with "WHERE ROWNUM = nn"; ROWNUM is a pseudocolumn but as it is assigned as rows are retreived (ie before they are sorted according to 'order by') it still may not give you what you want.
In any case to retrieve the last row of a query is not so easy, you would have to do something like

select * from emp
where rownum = (select count(*) from emp)

Better to reverse the query and
select ..
where rownum = 1

Hope this helps. If you have access to MetaLink there is plenty more about rownum.

Graham
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Deepak Extross
Honored Contributor

Re: only retrive LAST rown of sql query

If you're talking Informix Dynamic Server, try "SELECT FIRST 1 * FROM .... WHERE ... ORDER BY..."
Of course, you'll have to reverse your Order-by clause as well.
Magdi KAMAL
Respected Contributor

Re: only retrive LAST rown of sql query

Hi Ian,

In relational database, there is no order in the extraction of rows, since it's based on algebric sets which do not support order between the constituants of these sets.

So, suppose that you are issuing a sql statement that matches 10 rows, the output order of these rows will be a random order and no matter to fix which one appear at last or at the begining.
Then, if you try to print only one row ( with special mechanism ) you will have a different result each time you run this sql statement while data didn't changed.

And this is completely false !

Conclusion : In relational algebra, these is no order in sets.

Hope this helps.
Magdi
Eric Ladner
Trusted Contributor

Re: only retrive LAST rown of sql query

If you are talking Oracle/SQL*Plus, try this:

echo "set heading off pages 0 feed off heads off
select * from tab;" | sqlplus -s user/pass | tail -1

Substitute your SQL, of course.

Not a total Oracle solution, but works.
Reinhard Burger
Frequent Advisor

Re: only retrive LAST rown of sql query

Hi
if you can order on one of the fileds use this sql syntax

select ename,job from
(select ename,job from emp order by job,ename desc)
hwere rownum <=1;

that should retrieve the last record in the ordered output of the query.

Replace the column names and table name by what you need
keep it simple