I'm trying to execute a MYSQL command which grabs all the rows with the given UserID, sorts them by date, then returns only the first 5.
The command to sort is
ORDER BY date
and the command to get the last 5 is
WHERE ROWNUM <= 5
The WHERE comes before the ORDER, so it's backwards. So I figured I have to have a Mysql statement within a mysql statement.
Here is my attempt. I was getting an alias error, so i added the AS T1 to the command.
SELECT * FROM
(SELECT voting_id, caption_uid, voting_date, rating FROM voting
WHERE user_id = $inUserID AS T1
ORDER BY voting_date)
WHERE ROWNUM <= 5 AS T2;
Any ideas?
As you are working with MySQL, why not use the LIMIT clause, to only keep the first 5 results ?
select *
from your_table
order by your_column
limit 0, 5
Quoting a portion of the manual page for select :
The LIMIT clause can be used to constrain the number of rows
returned by the SELECT statement. LIMIT takes one or two numeric
arguments, which must both be nonnegative integer constants (except
when using prepared statements).
With two arguments, the
first argument specifies the offset of the first row to return, and
the second specifies the maximum number of rows to return.
Try this:
select voting_id, caption_uid, voting_date, rating from voting
where user_id = $inUserID
order by voting_date
limit 5
Edit:
It is not clear whether you want the first or last 5. If you want the last five then the order by should be:
order by voting_date desc
Try this query
Select columns from table_name where user_id =x order by dateField desc Limit 5
I am confuse with your title, and your question body.
You mentioned last 5 records. in the first sentence of your question, it is first 5.
A few words later, it is last 5 again.
If you want to get last 5, you may use this
select * from table order by date limit (select count(*)-5 from table),5
if you need to take care of less than 5 total records, you need to use the following:
select * from table order by date limit (select case when count(*)>=5 then count(*)-5 else 0 end case from table),5
If you can use stored procedure, the performance will be faster when you are in the 2nd example.
declare @total;
select @total = count(*) from `table`;
case when @total<5 then
@total=0;
else //mysql require case / else
end case;
select * from `table` order by `date` limit @total, 5
