View Full Version : SQL select quesion

September 11th, 2008, 02:11 AM
I have a table with a field named "book_id". How do I write a select statement to get the value for the book_id in the last record in the table?

I tried

SELECT LAST(book_id) FROM phototable

and it didn't work

September 11th, 2008, 02:32 AM
in a relational database, rows (records) do not have a relationship among themselves. there is also no real understanding of first/last record.

September 11th, 2008, 02:35 AM
give this a try:

select max(book_id) from phototable

September 11th, 2008, 02:48 AM
give this a try:

select max(book_id) from phototable

good point, but is book_id auto_increment or could it be the book;s ISDN number?

September 11th, 2008, 03:18 AM
Thanks to you both.

select max(book_id) from phototable

works in this particular case, but I see slavik's point that it would only work in certain cases.

September 11th, 2008, 01:27 PM
Oracle has rownum - seq number of the row in resultset. Other databases might have similar tricks. It's ridiculous how unstandard the 'SQL standard' is. Pure mirage.

get last 10 records (gets you first, but sorted as descending)

SELECT (some fields) FROM ... WHERE ... rownum < = 10 ORDER BY field DESC

Many databases can also return id of last inserted ID, which is probably what you want.

September 11th, 2008, 09:52 PM
select book_id, COUNT(book_id) offset from phototable LIMIT offset, 1;

This would probably do the trick too in mysql or oracle