I have a table with a number of columns and rows that are ordered by the variable artID. I need to get the last entry in the table, and I've been having trouble with this. I was using MAX() and was able to get the maximum artID, but I was unable to get the whole row. This is the statement I used, and by the way I'm using mysql:

SELECT MAX(artID) AS artID FROM articles;

I'm going to need to select the last row, and then the second to last row, and then the third to last row, until I get to the eighth row any ideas? It would be easy if I could use a variable in the sql statement, but I don't think I can. I also think that it'll be easier if I can get each row one at a time. I'd appreciate any thoughts on this, as I'm stumped.

Thanks.

I had success using the count() function, to achieve a similar effect i think. That was Oracle tho, not mySQL...

to get the last row of a table, I found I could do it like this: -

It basically lists all the rows in decreasing artid order, and only select the first 1 (which is the last row in the table as it's reversed). To get the second from last: -

when limit is given two params, it uses the first number as an offset, and the second number as the number of rows to get. If you wanted you could get all last 8 rows by saying .....limit 0,8.

HTH

glj

That was just what I was looking for. Thanks alot for your help.

------------------------------------------------------------------------

In order to retrieve the last row of a table for MS SQL database 2005, You can use the following query:

select top 1 column_name from table_name order by column_name desc;

// Note: To get the first row of the table for MS SQL database 2005, You can use the following query:

select top 1 column_name from table_name;

Thanks,
Deepali Shahabadi

This may be a new record----this thread is 7 1/2 years old.

Deepali;
I'd be willing to bet that the OP is no longer around. It is generally not a good idea to dig up old threads like this.

BUT---Welcome to LQ!!

Wow, haven't even been on the site for a few years. Welcome back me : )

Perhaps generally not so, but when google sends me in to a thread matching my issue with a tardy, correct response, it increases the worth of LQ tenfold.

Just my two cents. This site has been a shining star for as long as I can remember using Linux. Thank you to the moderators that turn the noise into signal.

My on the original question:

The standard SQL method is to us a WHERE clause to find specific rows in a table. That's primarily because SQL tables are not, normally, ordered in any fixed sense, and questions like "find the last row" are almost always meaningless without a precise definition of what is meant by the word "last" in the question. The WHERE clause is usually that precise definition.

So, for the question first poised by oulevon, where the id of the record that was desired was indicated to be SELECT MAX(artID) AS artID FROM articles, the "standard" SQL select would be

SELECT * from articles A WHERE A.artID = (SELECT MAX(artID) FROM articles);

Depending on the syntax preferred by the specific implementation of SQL being used, the local alias of for the "articles" table (the "A" may differ from that above, but the use of the nested SELECT to define the row is, generally, the preferred approach.

<edit>
And I wasn't even a LQ member 7 years ago . . .
</edit>

Use a natural key instead of a physical locator and there won't be a problem to solve. That's the difference between logic and physical design actually. Relational databases exist to abstract the data from the physical storage details/mechanisms. What does max(artID) really represent? Probably the last article to be published or reviewed or something like that, which usually means searching on a date/time field.

To quote the always diplomatic and eloquent Mr. Joe Celko:

So does the Mr Celko want id fields removed and table joins done by real data...?

Nonsense. There was no "stop" rule in that statement, so we can remove that last instruction leaving nothing, which can not contain one instruction that can not work. Thus the perfect program is an empty program, and we welcome all programmers to the beauty of Zen.