By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,537 Members | 2,172 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,537 IT Pros & Developers. It's quick & easy.

Subquery help

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a
>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

P: n/a
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

P: n/a
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

P: n/a
>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

P: n/a
"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

P: n/a

"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

P: n/a
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

P: n/a
"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.