[web] SQL challenge
Author
It's easy to group/collate data in SQL:
select max(Timestamp) from FileVersion where Timestamp < "20051205" group by FileId
This would get the latest timestamp on a file version from my database for each file, up to and not including December 5, 2005. (Please no comments about using a DateTime type, this is SQLite).
The question is, how do I get the whole row where Timestamp=max(Timestamp)? I could try to hack it like this:
select FileVersion.* from FileVersion inner join (select first(FileId), max(Timestamp) as MaxTimestamp from FileVersion where Timestamp < "20051205" group by FileId) mts on mts.FileId=FileVersion.FileId
It just feels so clunky, and frankly I'm pretty sure it's not even valid SQL. Does anyone have a better idea?
~BenDilts( void );
This seemed to work ok in MySQL:
select * from FileVersion where Timestamp = (select max(Timestamp) from FileVersion where Timestamp < "20051205")
I don't know whether SQLite can deal with the nesting, but if My can do it, it can't be that complicated.
select * from FileVersion where Timestamp = (select max(Timestamp) from FileVersion where Timestamp < "20051205")
I don't know whether SQLite can deal with the nesting, but if My can do it, it can't be that complicated.
Author
I guess this is kind of what I was going for:
select * from FileVersion inner join (select FileVersionId as fvid, max(Timestamp) from FileVersion group by FileId) on fvid=FileVersion.FileVersionId
It gets the job done. I just wish SQL had a little easier syntax for doing this, since I've come on similar problems several times in the past.
~BenDilts( void );
select * from FileVersion inner join (select FileVersionId as fvid, max(Timestamp) from FileVersion group by FileId) on fvid=FileVersion.FileVersionId
It gets the job done. I just wish SQL had a little easier syntax for doing this, since I've come on similar problems several times in the past.
~BenDilts( void );
Author
Yours is syntactically correct but misses the idea of what information I was trying to find. I wanted more than one row back--I wanted one row for each FileId in the table FileVersion, where the row had all the data for that FileId with the maximum timestamp. Yours just gave me the one row in the whole table with the maximum timestamp.
~BenDilts( void );
~BenDilts( void );
how about:
SELECT TOP 1 ( * ) FROM FileVersion WHERE Timestamp < "20051205" ORDER BY Timestamp DSC
SELECT TOP 1 ( * ) FROM FileVersion WHERE Timestamp < "20051205" ORDER BY Timestamp DSC
Author
Again, your solution would be great if I wanted to get one result per query (run a query per FileId), but remember that I need to group the rows by another field in addition to getting the data associated with the maximum timestamp.
A table containing the newest filetime for each file:
This is for MySQL 4.1+. Don't know if SQLite can handle this but I think it will.
SELECT FiletimeFROM FileVersion f1WHERE FileTime=(SELECT MAX(f2.FileTime) FROM FileVersion f2 WHERE f1.FileId = f2.FileId AND f2.FileTime < '20051205');This is for MySQL 4.1+. Don't know if SQLite can handle this but I think it will.
Author
Sander: Exactly what I wanted. A general question: Is it generally faster to do a WHERE clause or an inner join on a nested query?
~BenDilts( void );
~BenDilts( void );
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
