Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on May 7th, 2012  |  syntax: None  |  size: 2.24 KB  |  hits: 15  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. SQL LIMIT to get latest records
  2. tbl_items
  3. ---------------------------------------------
  4. item_id | item_name | item_value | timestamp
  5. ---------------------------------------------
  6.  
  7. tbl_categories
  8. -----------------------------
  9. cat_id | item_id | timestamp
  10. -----------------------------
  11.        
  12. SELECT e.item_id, e.item_value
  13.   FROM tbl_items AS e
  14.   JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  15.   LIMIT 25
  16.        
  17. SELECT e.item_id, e.item_value
  18.   FROM tbl_items AS e
  19.   JOIN tbl_categories AS cat WHERE e.item_id = cat.item_id AND cat.cat_id = 6001
  20.   ORDER BY e.timestamp
  21.   LIMIT 25
  22.        
  23. tbl_items
  24.  
  25. +---------------------+--------------+------+-----+---------+-------+
  26. | Field               | Type         | Null | Key | Default | Extra |
  27. +---------------------+--------------+------+-----+---------+-------+
  28. | item_id             | int(11)      | NO   | PRI | 0       |       |
  29. | item_name           | longtext     | YES  |     | NULL    |       |
  30. | item_value          | longtext     | YES  |     | NULL    |       |
  31. | timestamp           | datetime     | YES  |     | NULL    |       |
  32. +---------------------+--------------+------+-----+---------+-------+
  33.  
  34. tbl_categories
  35.  
  36. +----------------+------------+------+-----+---------+-------+
  37. | Field          | Type       | Null | Key | Default | Extra |
  38. +----------------+------------+------+-----+---------+-------+
  39. | cat_id         | int(11)    | NO   | PRI | 0       |       |
  40. | item_id        | int(11)    | NO   | PRI | 0       |       |
  41. | timestamp      | datetime   | YES  |     | NULL    |       |
  42. +----------------+------------+------+-----+---------+-------+
  43.        
  44. SELECT e.item_id, e.item_value
  45.   FROM
  46.      tbl_items AS e
  47.   JOIN
  48.      tbl_categories AS cat
  49.         on e.item_id = cat.item_id
  50.   WHERE  
  51.      cat.cat_id = 6001
  52.   ORDER BY
  53.      e.timestamp
  54.   LIMIT 25
  55.        
  56. create index idx_1 on tbl_categories( cat_id, item_id)
  57.        
  58. create index idx_2 on tbl_items( item_id, timestamp)
  59.        
  60. select T.cat_id, T.item_id, T.item_value from
  61.   (SELECT cat.cat_id, e.item_id, e.item_value
  62.    FROM
  63.      tbl_items AS e
  64.    JOIN
  65.      tbl_categories AS cat
  66.         on e.item_id = cat.item_id
  67.    ORDER BY
  68.      e.timestamp
  69.    LIMIT 25
  70.   ) T
  71.   WHERE  
  72.      T.cat_id between 6001 and 6012
  73.   ORDER BY
  74.      T.cat_id, T.item_id
create a new version of this paste RAW Paste Data