my table is
name (VARCHAR), created_on (DATETIME DEFAULT CURRENT_TIMESTAMP),
modified_on (DATETIME)
When a new record is created, it gets a value in the created_on col,
which is then not changed subsequently, but the modified_on col is
empty. Whenever the record is updated, the modified_on col is changed.
--I want the last 10 records
--query:
SELECT name, created_on, modified_on FROM (
--created
--sub-query1:
SELECT name, created_on, modified_on FROM table ORDER BY created_on
DESC LIMIT 0, 10
--but, also include
UNION
--records in the table modified later than any of the records found in
subquery1
--sub-query2:
SELECT name, created_on, modified_on FROM table ORDER BY modified_on
DESC LIMIT 0, 10
)
ORDER BY modified_on DESC
LIMIT 0, 10
Well, besides the fact that the above attempt is clumsy, it doesn't
work... SQLite scolds me that "ORDER BY should come after UNION" except
if I do that I won't get the last 10 records in each sub-query.
There must be a better way to query, or perhaps, even modding my table
structure to better accomplish such a query. Any suggestions?
--
Puneet Kishor
