Page 1 of 1
Mysql query the second to last record in a table
Posted: Fri Oct 09, 2009 12:46 am
by edawson003
How can I construct a mysql query that would pull up the 2nd to last entry in a database based on a date field like entry date?
I know how to pull up the last entry.
Code: Select all
SELECT *
FROM Person
ORDER BY EntryDate
DESC LIMIT 1
Re: Mysql query the second to last record in a table
Posted: Fri Oct 09, 2009 1:42 am
by flying_circus
Code: Select all
SELECT *
FROM Person
ORDER BY EntryDate DESC
LIMIT 1, 1
Limit 0,1 should return the the last row, limit 1 (offset), 1 (num rows to return) should return the 2nd to last row only.
Re: Mysql query the second to last record in a table
Posted: Fri Oct 09, 2009 8:43 am
by edawson003
Awesome, thanks! I've struggling with that one for a while.
Re: Mysql query the second to last record in a table
Posted: Fri Oct 09, 2009 11:52 am
by califdon
The order of rows in a table of a relational database is undefined. When you start basing the logic of your application on where a row is located in the table, you are not using the rules of relational databases and the results are not predictable. Location of data should always be based on the content of data in the rows, not on position within the table. To do otherwise is to ignore the rules upon which relational databases are founded.
Re: Mysql query the second to last record in a table
Posted: Fri Oct 09, 2009 7:50 pm
by josh
Wouldn't the limit stuff return the 2nd from "first" rows anyways

I agree tho that approach is messy, add a order column and then
select MAX( `order` ) from `table` where 1 ( this will give you the "last" row's order #, then subtract 1 and query for that row. Or better yet pull the whole collection ( depending on size ) and do it with code
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 6:45 am
by edawson003
Actually, I tested the below code and it returned the 2nd last entry in my table. The fact that I order by entry date (which is a datetime) in descending order allowed for the "LIMIT" to pull from the bottom, so to speak.
Code: Select all
SELECT *
FROM Person
ORDER BY EntryDate DESC
LIMIT 1, 1
I am trying to wrap my head around the "adding an order" column suggestion. I am a bit reluctant to create an order column, cuz now that is one extra field I have to code for. Couldn't I just utilize the entry date field as a way to delineate order? Could you please elaborate why
select MAX( `order` ) from `table` where 1 would be a better solution?
Just for my own edification, under what circumstances, would basing the logic of an application where a row is located in the table produce upredictable results? Even with the
ORDER BY EntryDate DESC, do you still think it could end up messy?
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 11:57 am
by josh
edawson003 wrote: I am a bit reluctant to create an order column, cuz now that is one extra field I have to code for. Couldn't I just utilize the entry date field as a way to delineate order?
Yes, an order column is only used in place of "implicit" ordering. ( For ex if the rows are inserted in a certain order doesnt mean a select will return them in that order, always use an ORDER BY clause in the query if order is important, which you did which is good )
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 12:36 pm
by edawson003
Oh, cool. Thanx! I just wanted to fully vet the implications of either option, so I can make the best choice. Your feedback is much appreciated. How is it over there in South Florida? I got fam out there. I'm due for another visit

. I luv South Beach.
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 12:39 pm
by califdon
edawson003 wrote:Actually, I tested the below code and it returned the 2nd last entry in my table. The fact that I order by entry date (which is a datetime) in descending order allowed for the "LIMIT" to pull from the bottom, so to speak.
Code: Select all
SELECT *
FROM Person
ORDER BY EntryDate DESC
LIMIT 1, 1
I am trying to wrap my head around the "adding an order" column suggestion. I am a bit reluctant to create an order column, cuz now that is one extra field I have to code for. Couldn't I just utilize the entry date field as a way to delineate order? Could you please elaborate why
select MAX( `order` ) from `table` where 1 would be a better solution?
Just for my own edification, under what circumstances, would basing the logic of an application where a row is located in the table produce upredictable results? Even with the
ORDER BY EntryDate DESC, do you still think it could end up messy?
This may be an issue of interpretation. You are saying (and thinking, I'm sure) that you want to retrieve the "2nd last entry in my table", but that's NOT what you are actually doing at all. That language implies that you are speaking of the order in which the data was entered, and presumably is physically stored. That concept has no meaning in relational databases. What I believe you actually mean is the next-to-maximum date in the Person table, which is entirely different. That is what your query, above, is selecting. It has nothing to do with "where" it is stored in the table. So you are doing the right thing, but you are describing it poorly.
Read josh's most recent post very carefully. You don't need an additional column in your case, since you already have the date column. But the way you described what you want misled both of us into believing that you wanted to find the "2nd last entry" in your table, which is NOT what you wanted. A table should be considered just a "bucket" of data. Queries are the way you extract the data you need, based on the data itself, not where you
think it might be stored within the table.
As to your question about when a reliance on position in a table would produce unpredictable results, it would be unpredictable in every case where a row has been deleted or the table was refreshed in some backup operation. The point is that SQL queries are based on relational algebra, which in turn is based on the relationships inherent in the data and is not affected at all by position in the storage mechanism, which is what a table is. Relational theory, indeed, does not even use the word "table" at all. It uses the word "relation", for which a table is a physical implementation.
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 1:11 pm
by edawson003
Oh I see. I thought the part in my first post where I say "
the 2nd to last entry in a database based on a date field like entry date?" was pretty clear (after-all, flying_circus got it and his suggestion was spot on), but that's okay; it's all semantics.
Sounds like your well versed in relational database theory, correct terminology and such. Forgive me, I'm just a hack

!
I appreciate the feedback, I learned a lil something from it all the same. Thanks!
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 2:36 pm
by califdon
There's nothing to forgive you for. Everyone has to start at the beginning. Yes, I've been in the database business for longer than most, taught it for years in college, and made a living developing databases for commercial clients. The problem for most beginners is that they often hang onto mental images of what their data looks like when displayed. Spreadsheets became so popular because they actually do store and display their data pretty much the same way, so that the first row that you see on the screen is actually the first row in the file where it is stored. That makes it easy to visualize. Databases are fundamentally different. The relational database 'model' that is the basis of SQL queries is a purely mathematical model, developed by Dr. E. F. Codd, a mathematician at IBM around 1970 and documented by Chris Date in his landmark book,
An Introduction to Database Systems, which is now in something like its 8th edition and still used around the world in computer science courses. I had the privilege of attending one of his seminars many years ago. Anyway, the point is that relational databases are definitely not intuitive and if you don't follow the rules of relational algebra, you're inviting all sorts of problems when you try to make queries do what you need them to do. In relational database work, it's seldom a matter of "which way is better?" and more often simply "this is the proper way, and any other way is faulty." That's a hard lesson to teach, but it's just true.
If you're interested in pursuing this field of study further (and you should, if you plan to do very much more with database design), I recommend Chris Date's book, at the high end of the scale, and such online tutorials as:
http://www.learn-sql-tutorial.com/DatabaseBasics.cfm
http://www.deeptraining.com/litwin/dbde ... esign.aspx
http://java.sun.com/docs/books/tutorial ... abase.html
http://www.kirupa.com/developer/php/rel ... design.htm
Re: Mysql query the second to last record in a table
Posted: Sun Oct 11, 2009 3:00 pm
by edawson003
I will defitely study those tutorials and check out that book. Thanks for the plug.