Subquery help

[Neeper]

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.

[Gordon Burditt]

>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

[Neeper]

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

[Neeper]

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

[Gordon Burditt]

>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

[Thomas Bartkus]

"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

[Thomas Bartkus]

"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

[Evil Bert]

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

[Thomas Bartkus]

"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