[RESOLVED] Simple query MySQL question

[Blue1974]

I've been trying to get a statement to work that will pull the last five records in my table regardless of the number of records.

Can someone show me what the statement would look like. My table is called students.

This is what I've got so far:

select * from students limit select count(*) from students - 5, 5; 

I've also tried setting the count equal to a variable named studentcount and used it like this:

select * from students limit studentcount -  5, 5; 

I would like to be able to understand the long way if anyone knows how it can work?

[kows]

do you have a 'date added' field that you could sort by? if so:

SELECT * FROM students ORDER BY date_added DESC LIMIT 0, 5

otherwise, you should have some sort of ID to sort by -- would that work? it would be the same method as above, but using the ID field instead of date_added.

[Blue1974]

No, there's wasn't a date added field. Fields look like this.

The problem asks: Display the last five records. Your select should work regardless of the number of records.

+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| id | int(8) | NO | PRI | NULL | auto_increment |
| lname | varchar(12) | NO | | NULL | |
| fname | varchar(12) | NO | | NULL | |
| credits | int(11) | YES | | NULL | |
| grade_pts | int(11) | YES | | NULL | |
| advisor | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)


I tried doing it like this and did get results:

select * from students order by id desc limit 0,5;

If I see it properly, this takes the bottom five records and counts them from the bottom up. So I would start at record 21,20,19,18 and get to 17 but it's backwards. I need to get them starting at 17 - 21. I'm not suppose to use the id to order the records. The display of the records just involves the count and limit. I haven't figured out how it works yet.

If I knew the count was 21 before hand, I could use the statement:

select * from students limit 16,5;

The problem says no matter how many records there are, display the last five. I need to get it in the first select statement assuming I don't know what the count is, does that make any sense?

[Nightwalker83]

What about this:

$query = "select * from students order by  Name desc limit 0,5"; 

[SambaNeko]

I don't see how you'd do this with just count() and LIMIT, as LIMIT does not allow for expressions or sub-queries in its arguments (right?). kows' solution is the one I'd advise as well.

[kows]

well, what's the point in having the ID field there if you can't sort by it?

anyway, I found that using a prepared statement would work.

SET @skip=(SELECT COUNT(id) FROM test.students) - 5;
SET @numrows=5;

PREPARE STMT FROM 'SELECT * FROM test.students LIMIT ?, ?';

EXECUTE STMT USING @skip, @numrows;

I think this might only work in MySQL >= 5.0.27 though. and, even so, might not even be a convenient way of querying! but, it does what you want.

[SambaNeko]

well, what's the point in having the ID field there if you can't sort by it?

This kind of sounds like a textbook- or instructor-fashioned question that seeks a specific answer - not necessarily the most likely real-world application answer.

Even if you needed the last five rows to be sorted by ascending value rather than descending, that could be accomplished with a sub-query turned derived table:

SELECT * FROM (SELECT * FROM students ORDER BY id DESC LIMIT 0,5) t ORDER BY id ASC

[Nightwalker83]

This kind of sounds like a textbook- or instructor-fashioned question that seeks a specific answer - not necessarily the most likely real-world application answer.

I agree! It is likely that if it was a real world situation that the company/person would just do as kows suggests and use the id.