Re: [sqlite] SQL assistance with selecting the last n records inserted

[Jay Sprenkle]

> 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

[Puneet Kishor]

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

[Jay Sprenkle]

> 
> 
> > 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

[Derrell . Lipman]

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

[Puneet Kishor]

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

[Jay Sprenkle]

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

[Puneet Kishor]

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