Sign in to follow this  

[web] SQL challenge

This topic is 4418 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 );

Share this post


Link to post
Share on other sites
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 );

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
A table containing the newest filetime for each file:

SELECT Filetime
FROM FileVersion f1
WHERE 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.

Share this post


Link to post
Share on other sites
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 );

Share this post


Link to post
Share on other sites
I think that depends on the query.

e.g outer query without where = 1000 results, with where is 20 results
inner query without where = 500 results, with where is 7 results

where on inner query = 1000 * 7 = 7000 searches + 1000 where's
where on outer query = 500 * 20 = 10000 searches + 1 where

If the 1000 where's on the inner query are faster than searching the 3000 extra results, then where on the inner query is faster. Indexes play a large part too. Profile, profile, profile. That's all I can recommend.

Share this post


Link to post
Share on other sites

This topic is 4418 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this