469,106 Members | 2,289 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,106 developers. It's quick & easy.

Subquery help

I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
Please help.
Nov 2 '05 #1
8 1820
>I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
Please help.
What version of MySQL are you using? Subqueries didn't start working
until about 4.1.something. Also, it doesn't work in 5.0.15 unless you
change it to:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10) a
ORDER BY timestamp ASC;


since it seems to want an alias name for the derived table.

Gordon L. Burditt
Nov 2 '05 #2
The server is running:

PHP Version 4.3.11
--

On Wed, 02 Nov 2005 07:50:27 -0000, go***********@burditt.org (Gordon
Burditt) wrote:
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
Please help.


What version of MySQL are you using? Subqueries didn't start working
until about 4.1.something. Also, it doesn't work in 5.0.15 unless you
change it to:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10) a
ORDER BY timestamp ASC;


since it seems to want an alias name for the derived table.

Gordon L. Burditt


Nov 2 '05 #3
I tried the query with 'a' after ...LIMIT 10) but still doesn't work.

On Wed, 02 Nov 2005 07:50:27 -0000, go***********@burditt.org (Gordon
Burditt) wrote:
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
Please help.


What version of MySQL are you using? Subqueries didn't start working
until about 4.1.something. Also, it doesn't work in 5.0.15 unless you
change it to:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10) a
ORDER BY timestamp ASC;


since it seems to want an alias name for the derived table.

Gordon L. Burditt


Nov 2 '05 #4
>The server is running:

PHP Version 4.3.11
What version of *MYSQL* are you running?

Gordon L. Burditt

--

On Wed, 02 Nov 2005 07:50:27 -0000, go***********@burditt.org (Gordon
Burditt) wrote:
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;
Please help.


What version of MySQL are you using? Subqueries didn't start working
until about 4.1.something. Also, it doesn't work in 5.0.15 unless you
change it to:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10) a
ORDER BY timestamp ASC;


since it seems to want an alias name for the derived table.

Gordon L. Burditt

Nov 2 '05 #5
"Neeper" <ne****@hotmail.com> wrote in message
news:t6********************************@4ax.com...
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;


You could do this with a subquery *if available* in your Version of MySQL

SELECT *
FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) As X
ORDER BY timestamp ASC;

But the use of temporary tables is equivalent and available further back in
MySQL revision history.

CREATE TEMPORARY TABLE X
SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10;

SELECT * FROM X ORDER BY timestamp ASC;

DROP TABLE X;

These (3) queries produce exactly the same result. I suspect that
subqueries simply use temporary tables behind the scenes anyway. You can
always do it yourself up front as shown.

Thomas Bartkus

Nov 2 '05 #6

"Neeper" <ne****@hotmail.com> wrote in message
news:t6********************************@4ax.com...
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;


You could do this with a subquery *if available* in your Version of MySQL.

SELECT *
FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) As X
ORDER BY timestamp ASC;

But the use of temporary tables is equivalent and available further back in
MySQL revision history.

CREATE TEMPORARY TABLE X
SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10;

SELECT * FROM X ORDER BY timestamp ASC;

DROP TABLE X;

These (3) queries produce exactly the same result. I suspect that
subqueries simply use temporary tables behind the scenes anyway. You can
always do it yourself up front as shown.

Thomas Bartkus
Nov 2 '05 #7
I'm using mySQL 4.0.25, I'm not sure if this supports subqueries.


On Wed, 2 Nov 2005 10:08:54 -0600, "Thomas Bartkus"
<th***********@comcast.net> wrote:

"Neeper" <ne****@hotmail.com> wrote in message
news:t6********************************@4ax.com.. .
I'm trying to pull the last 10 records from a transactions from a
table using this query:

SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10

But I want to display the rows in ascending order by timestamp. I
can't get the subquery below to work and not sure why:
SELECT *
FROM (SELECT *
FROM transactions
ORDER BY timestamp DESC
LIMIT 10)
ORDER BY timestamp ASC;


You could do this with a subquery *if available* in your Version of MySQL.

SELECT *
FROM (SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10) As X
ORDER BY timestamp ASC;

But the use of temporary tables is equivalent and available further back in
MySQL revision history.

CREATE TEMPORARY TABLE X
SELECT * FROM transactions ORDER BY timestamp DESC LIMIT 10;

SELECT * FROM X ORDER BY timestamp ASC;

DROP TABLE X;

These (3) queries produce exactly the same result. I suspect that
subqueries simply use temporary tables behind the scenes anyway. You can
always do it yourself up front as shown.

Thomas Bartkus


Nov 2 '05 #8
"Evil Bert" <ne****@hotmail.com> wrote in message
news:a0********************************@4ax.com...
I'm using mySQL 4.0.25, I'm not sure if this supports subqueries.


I know that it *does* support the temporary table solution I indicated.
I used this trick all the time with 4.0 in order to work around the lack of
subqueries.

And now that we have subqueries available in our Ver 4.1.5 -
- I find I still prefer to use the temporary tables.

Thomas Bartkus
Nov 2 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by leegold2 | last post: by
6 posts views Thread by phillip.s.powell | last post: by
2 posts views Thread by reap76 | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.