Re: How to sort last n entries?
At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ A censor is a man who knows more than he thinks you ought to. -- Laurence J. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
Chris Sansom schrieb: At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n Last n entries means I want the last (highest) n ids. And that result sorted by date. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
At 10:41 +0200 15/9/06, Dominik Klein wrote: I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? ORDER BY `date` DESC LIMIT n Last n entries means I want the last (highest) n ids. And that result sorted by date. ORDER BY `id` DESC, `date` ASC LIMIT n I think :-) Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
And if your MySQL version does NOT support sub queries you can probably just create a temporary table and then sort that one. /Peter -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:28 PM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: FW: How to sort last n entries? Assuming your MySQL version supports sub queries you do like this. I have never done sub queries my self, but I know the theory :) SELECT * FROM table WHERE id = (SELECT id FROM table ORDER BY id DESC) ORDER BY date /Peter Lauri www.lauri.se - personal www.dwsasia.com - company (Web Development Bangkok Thailand) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 3:41 PM To: mysql@lists.mysql.com Subject: How to sort last n entries? I have a table with primary key id. Another field is date. Now I want the last n entries, sorted by date. Is this possible in one SQL statement? Thanks for your help Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
You are correct. So that maybe leaves you with a temporary table then :) -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to sort last n entries?
CREATE TEMPORARY TABLE tabletemp SELECT * FROM table ORDER BY id DESC LIMIT 30; SELECT * FROM tabletemp ORDER BY date; -Original Message- From: Dominik Klein [mailto:[EMAIL PROTECTED] Sent: Friday, September 15, 2006 4:45 PM To: mysql@lists.mysql.com Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to sort last n entries?
This might work, I've used this syntax to select and sort from a UNION. SELECT * FROM (SELECT * FROM table ORDER BY id DESC LIMIT n) AS ltable ORDER BY datefield DESC According to the documentation you can use limit in subqueries: A subquery can contain any of the keywords or clauses that an ordinary SELECT can contain: DISTINCT, GROUP BY, ORDER BY, LIMIT,... - Original Message - From: Dominik Klein [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, September 15, 2006 5:44 AM Subject: Re: How to sort last n entries? Peter Lauri schrieb: SELECT * FROM table WHERE id=(SELECT id FROM table ORDER BY id DESC) ORDER BY date This does not limit it to n entries (order by date limit n is not sufficient as I need last (highest) n ids). And afaik, limit is not allowed in sub-queries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
