LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > Programming
User Name
Password
Programming This forum is for all programming questions.
The question does not have to be directly related to Linux and any language is fair game.

Notices

Reply
 
Search this Thread
Old 09-11-2001, 10:35 PM   #1
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Rep: Reputation: 30
SQL statement to get the last row in a table


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.
 
Old 09-12-2001, 02:18 AM   #2
acid_kewpie
Moderator
 
Registered: Jun 2001
Location: UK
Distribution: Gentoo, RHEL, Fedora, Centos
Posts: 43,378

Rep: Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963Reputation: 1963
I had success using the count() function, to achieve a similar effect i think. That was Oracle tho, not mySQL...
 
Old 09-12-2001, 03:30 AM   #3
glj
Member
 
Registered: Jul 2001
Location: London
Distribution: RH 9
Posts: 151

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

Code:
select * from articles order by artid desc limit 1;
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: -

Code:
select * from articles order by artid desc limit 1,1;
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
 
Old 09-12-2001, 01:44 PM   #4
oulevon
Member
 
Registered: Feb 2001
Location: Boston, USA
Distribution: Slackware
Posts: 435

Original Poster
Rep: Reputation: 30
That was just what I was looking for. Thanks alot for your help.
 
Old 02-26-2009, 06:20 AM   #5
Deepali Shahabadi
LQ Newbie
 
Registered: Feb 2009
Posts: 1

Rep: Reputation: 0
Smile Retrieve the last row of a table for MS SQL database 2005

Quote:
Originally Posted by oulevon View Post
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.
------------------------------------------------------------------------

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
 
Old 02-26-2009, 07:06 AM   #6
pixellany
LQ Veteran
 
Registered: Nov 2005
Location: Annapolis, MD
Distribution: Arch/XFCE
Posts: 17,802

Rep: Reputation: 728Reputation: 728Reputation: 728Reputation: 728Reputation: 728Reputation: 728Reputation: 728
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!!
 
Old 02-27-2009, 03:21 AM   #7
glj
Member
 
Registered: Jul 2001
Location: London
Distribution: RH 9
Posts: 151

Rep: Reputation: 30
Wow, haven't even been on the site for a few years. Welcome back me : )
 
Old 04-02-2009, 08:20 AM   #8
digitalsushi
LQ Newbie
 
Registered: Sep 2003
Posts: 2

Rep: Reputation: 0
Quote:
Originally Posted by pixellany View Post
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!!
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.
 
Old 04-02-2009, 10:45 AM   #9
PTrenholme
Senior Member
 
Registered: Dec 2004
Location: Olympia, WA, USA
Distribution: Fedora, (K)Ubuntu
Posts: 4,147

Rep: Reputation: 330Reputation: 330Reputation: 330Reputation: 330
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>

Last edited by PTrenholme; 04-02-2009 at 10:47 AM.
 
Old 04-02-2009, 11:03 AM   #10
Crito
Senior Member
 
Registered: Nov 2003
Location: Knoxville, TN
Distribution: Kubuntu 9.04
Posts: 1,168

Rep: Reputation: 53
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:
Quote:
Technically, these are not really keys at all, since they are attributes of the physical storage and are not even part of the logical data model. But they are handy for lazy, non-RDBMS programmers who do not want to research or think! This is the worst way to program in SQL.

Last edited by Crito; 04-02-2009 at 11:05 AM.
 
Old 04-02-2009, 11:03 PM   #11
graemef
Senior Member
 
Registered: Nov 2005
Location: Hanoi
Distribution: Fedora 13, Ubuntu 10.04
Posts: 2,376

Rep: Reputation: 147Reputation: 147
So does the Mr Celko want id fields removed and table joins done by real data...?
 
Old 04-03-2009, 08:30 PM   #12
PTrenholme
Senior Member
 
Registered: Dec 2004
Location: Olympia, WA, USA
Distribution: Fedora, (K)Ubuntu
Posts: 4,147

Rep: Reputation: 330Reputation: 330Reputation: 330Reputation: 330
Quote:
Every program has at least one bug and can be shortened by at least one instruction
-- from which, by induction, one can deduce that every program can be reduced to one instruction which doesn't work.
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.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
SQL question: Need to add a column from another table Hivemind Programming 3 09-19-2005 06:02 PM
help with simple sql statement sekelsenmat Programming 6 08-06-2005 12:01 PM
PHP script to check filetypes and put images into SQL table benrose111488 Programming 5 03-02-2005 01:57 AM
mysql sql statement help mrtwice Programming 4 12-02-2003 10:04 AM
SQL statement glj Programming 1 10-12-2001 09:29 AM


All times are GMT -5. The time now is 06:52 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration