Tricky Sql Query...?

[markiemrboo]

I have a little problem. What I need to do, is get the last 60 results from a database.... with a catch, after I have the last 60 results I need them in reverse order. Lets give an example...

 

SELECT timewhen,cpuusage FROM sigcpuusage ORDER BY timewhen DESC LIMIT 60;

 

Now, that'll get me the last 60 results, but then how do I go about reversing it back?

 

I tried doing a subselect to get a list of ids from the table in the reverse order, then reversing that output along the lines of...

 

SELECT id, timewhen, cpuusage FROM sigcpuusage WHERE id IN (SELECT id FROM sigcpuusage ORDER BY timewhen DESC LIMIT 5) ORDER BY timewhen ASC;

 

Which looks like it should work? But MySQL doesn't seem to like that. It throws an error complaining...

 

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id FROM sigcpuusage ORDER BY timewhen DESC LIMIT 5) ORDE

 

Any ideas? Maybe I will have to try that subselect in Postgres after eating..?

[Muddy]

not sur ewhat your asking but cant you use ASC instead of DESC?

[markiemrboo]

Figured someone might say that

 

No I can't. I need the last 60 rows entered in to the database, which is what my original query does, but then I need the output from that back in ascending order... if I just change the original query to sort ascending I am going to always get the same first 60 results that were entered in to the database.

[Nemo]

Figured someone might say that

 

No I can't. I need the last 60 rows entered in to the database, which is what my original query does, but then I need the output from that back in ascending order... if I just change the original query to sort ascending I am going to always get the same first 60 results that were entered in to the database.

506526[/snapback]

I don't think you can use an ORDER BY clause in a subquery.

 

You could always select the results into a temp table then select back out of the temp table in the proper order. Not as elegant, but it should work.

[markiemrboo]

I don't think you can use an ORDER BY clause in a subquery.

 

You could always select the results into a temp table then select back out of the temp table in the proper order.  Not as elegant, but it should work.

506528[/snapback]

 

That's an idea, i'll try that one.

 

It's not giving an error because of the order by in a subquery I can tell you that much. The subquery doesn't even want to work being as simple as...

 

SELECT id, timewhen, cpuusage FROM sigcpuusage WHERE id IN (SELECT id FROM sigcpuusage LIMIT 60) ORDER BY timewhen ASC;

 

Same error.

 

EDIT: Oh soddery! My proposed subselect query does do what I want it to do in Postgres. MySQL must just be broken. Phft.