Re: [sqlite] SQL assistance with selecting the last n records inserted
> and so it does. Gracias. ;-) > > Now I am off happily experimenting with applying other functions to > ORDER BY before ordering them. Thanks Derrell. You've used your super powered intellect well today! ;) --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
Re: [sqlite] SQL assistance with selecting the last n records inserted
On Sep 12, 2005, at 8:54 AM, [EMAIL PROTECTED] wrote: Jay Sprenkle <[EMAIL PROTECTED]> writes: If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc limit 10 except that if modified_on is null, you won't get that record. This variation should fix that problem: SELECT * FROM tbl ORDER BY MAX(created_on, COALESCE(modified_on, 0)) DESC LIMIT 10; and so it does. Gracias. ;-) Now I am off happily experimenting with applying other functions to ORDER BY before ordering them. -- Puneet Kishor
Re: [sqlite] SQL assistance with selecting the last n records inserted
> > > > If you just want 10 records with the highest modified or created time I > > think this will do it: > > > > select * > > from tbl > > order by max(created_on,modified_on) desc > > I didn't realize MAX could be used in ORDER BY. > > However, the above does not work. It seems to overlook all the records > which have a created_on date but no modified_on date. For example, even > the most latest record created but not yet modified doesn't show up in > the above query result. It seems to pull only those records which have > a valid value for both created_on and modified_on columns. Oh. Sorry, forgot about NULLS :( You could either wrap the modified_on field with a function to return the creation date if it's null, or assign a modified date to every record equal to the create date when the record is created.
Re: [sqlite] SQL assistance with selecting the last n records inserted
Jay Sprenkle <[EMAIL PROTECTED]> writes: > If you just want 10 records with the highest modified or created time I > think this will do it: > > select * > from tbl > order by max(created_on,modified_on) desc > limit 10 except that if modified_on is null, you won't get that record. This variation should fix that problem: SELECT * FROM tbl ORDER BY MAX(created_on, COALESCE(modified_on, 0)) DESC LIMIT 10; Derrell
Re: [sqlite] SQL assistance with selecting the last n records inserted
On Sep 12, 2005, at 8:43 AM, Jay Sprenkle wrote: On 9/12/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: 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 If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc I didn't realize MAX could be used in ORDER BY. However, the above does not work. It seems to overlook all the records which have a created_on date but no modified_on date. For example, even the most latest record created but not yet modified doesn't show up in the above query result. It seems to pull only those records which have a valid value for both created_on and modified_on columns. -- Puneet Kishor
Re: [sqlite] SQL assistance with selecting the last n records inserted
On 9/12/05, Puneet Kishor <[EMAIL PROTECTED]> wrote: > > 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 If you just want 10 records with the highest modified or created time I think this will do it: select * from tbl order by max(created_on,modified_on) desc limit 10 --- The Castles of Dereth Calendar: a tour of the art and architecture of Asheron's Call http://www.lulu.com/content/77264
[sqlite] SQL assistance with selecting the last n records inserted
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