only retrive LAST rown of sql query

[Ian McClement_1]

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

[Tom Geudens]

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

[Steve Lewis]

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 · Accepted solution]

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

[Deepak Extross]

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]

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]

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]

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