I have table documents (id, name, time). Is there a special sql command to set table limit by 10 rows?
- 2Are you looking to limit the amount of rows in the table, or the number of rows returned by a select statement?anothershrubery– anothershrubery2011-11-24 09:11:26 +00:00Commented Nov 24, 2011 at 9:11
- i need to limit amount of rows in the tabledojo– dojo2011-11-24 09:16:29 +00:00Commented Nov 24, 2011 at 9:16
- Well then see Haim's answer. It's not possible in any traditional sense.anothershrubery– anothershrubery2011-11-24 09:19:09 +00:00Commented Nov 24, 2011 at 9:19
- 1Also can I ask why you want to do this? I'm sure there is another way to achieve what you are looking for.anothershrubery– anothershrubery2011-11-24 09:20:13 +00:00Commented Nov 24, 2011 at 9:20
- in this table is information of saved documents name, so i'm using this table for displaying "latest 10 generated documents"dojo– dojo2011-11-24 09:23:09 +00:00Commented Nov 24, 2011 at 9:23
| Show 1 more comment
3 Answers
If you just want to display the "latest 10 generated documents", no need for a separate table. Just use a query on your existing table:
SELECT id, name, `time` FROM documents ORDER BY `time` DESC LIMIT 10 
Comments
no you could not set a limit on the mysql table, you can achive this with trigger that delete rows.
Comments
So, if you have a table like this:
CREATE TABLE documents ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR( 99 ) NOT NULL, `time` DATETIME NOT NULL ) ENGINE = MYISAM then you can use this horrible query to limit row numbers by recycling the row having the lowest id field:
INSERT INTO documents (id,name,time) SELECT IF( (SELECT COUNT(*) FROM documents) < 3 -- max row number you allow ,NULL -- just use autoincrement id ,(SELECT MIN(id) FROM documents) -- update row with smallest id ), -- your values to insert 'name' ,'2011-11-11' ON DUPLICATE KEY UPDATE id = (SELECT MAX(id)+1 FROM documents) -- new id -- your values again, now for update ,name = 'name' ,time = '2011-11-11' Somebody please confirm if this query is atomic, i think it is, but who knows…
