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]

Re: Subquery help

>I'm trying to pull the last 10 records from a transactions from a[color=blue]
>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.[/color]

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:
[color=blue]
>SELECT *
>FROM (SELECT *
> FROM transactions
> ORDER BY timestamp DESC
> LIMIT 10) a
>ORDER BY timestamp ASC;[/color]

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

Gordon L. Burditt

[Neeper]

Re: Subquery help

The server is running:

PHP Version 4.3.11


--

On Wed, 02 Nov 2005 07:50:27 -0000, gordonb.i6rbi@b urditt.org (Gordon
Burditt) wrote:
[color=blue][color=green]
>>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.[/color]
>
>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:
>[color=green]
>>SELECT *
>>FROM (SELECT *
>> FROM transactions
>> ORDER BY timestamp DESC
>> LIMIT 10) a
>>ORDER BY timestamp ASC;[/color]
>
>since it seems to want an alias name for the derived table.
>
> Gordon L. Burditt[/color]

[Neeper]

Re: Subquery help

I tried the query with 'a' after ...LIMIT 10) but still doesn't work.

On Wed, 02 Nov 2005 07:50:27 -0000, gordonb.i6rbi@b urditt.org (Gordon
Burditt) wrote:
[color=blue][color=green]
>>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.[/color]
>
>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:
>[color=green]
>>SELECT *
>>FROM (SELECT *
>> FROM transactions
>> ORDER BY timestamp DESC
>> LIMIT 10) a
>>ORDER BY timestamp ASC;[/color]
>
>since it seems to want an alias name for the derived table.
>
> Gordon L. Burditt[/color]

[Gordon Burditt]

Re: Subquery help

>The server is running:[color=blue]
>
>PHP Version 4.3.11[/color]

What version of *MYSQL* are you running?

Gordon L. Burditt[color=blue]
>
>
>--
>
>On Wed, 02 Nov 2005 07:50:27 -0000, gordonb.i6rbi@b urditt.org (Gordon
>Burditt) wrote:
>[color=green][color=darkred]
>>>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.[/color]
>>
>>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:
>>[color=darkred]
>>>SELECT *
>>>FROM (SELECT *
>>> FROM transactions
>>> ORDER BY timestamp DESC
>>> LIMIT 10) a
>>>ORDER BY timestamp ASC;[/color]
>>
>>since it seems to want an alias name for the derived table.
>>
>> Gordon L. Burditt[/color]
>[/color]

[Thomas Bartkus]

Re: Subquery help

"Neeper" <neeper@hotmail .com> wrote in message
news:t6rgm1d88c vilcl07h330ie1j cje7s9am8@4ax.c om...[color=blue]
> 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;[/color]

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]

Re: Subquery help


"Neeper" <neeper@hotmail .com> wrote in message
news:t6rgm1d88c vilcl07h330ie1j cje7s9am8@4ax.c om...[color=blue]
> 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;[/color]

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]

Re: Subquery help

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"
<thomasbartkus@ comcast.net> wrote:
[color=blue]
>
>"Neeper" <neeper@hotmail .com> wrote in message
>news:t6rgm1d88 cvilcl07h330ie1 jcje7s9am8@4ax. com...[color=green]
>> 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;[/color]
>
>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
>[/color]

[Thomas Bartkus]

Re: Subquery help

"Evil Bert" <neeper@hotmail .com> wrote in message
news:a0dim1dc45 or8hgk7cjumt8o0 utenu7hpd@4ax.c om...[color=blue]
> I'm using mySQL 4.0.25, I'm not sure if this supports subqueries.
>[/color]

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