hi,

i want to display the five most recent rows in my table (there is a
timestamp), but sorted in ascending order.

the problem is that when i call

SELECT * FROM spam ORDER BY time ASC LIMIT 5

it shows the first five entries in ascending order, but i want the last
five.

needless to say, it works fine with descending order..

thanks.

Mark wrote:
> hi,
>
> i want to display the five most recent rows in my table (there is a
> timestamp), but sorted in ascending order.
>
> the problem is that when i call
>
> SELECT * FROM spam ORDER BY time ASC LIMIT 5
>
> it shows the first five entries in ascending order, but i want the last
> five.
>
> needless to say, it works fine with descending order..
>
> thanks.
Do you want first five entrys in descending order or last five entrys
in ascending order or what ????

Mark wrote:
> hi,
>
> i want to display the five most recent rows in my table (there is a
> timestamp), but sorted in ascending order.
>
> the problem is that when i call
>
> SELECT * FROM spam ORDER BY time ASC LIMIT 5
>
> it shows the first five entries in ascending order, but i want the last
> five.
>
> needless to say, it works fine with descending order..
>
> thanks.

SELECT * FROM (SELECT * FROM spam ORDER BY time DESC LIMIT 5) t1 ORDER
BY time ASC;

strawberry wrote:
> Mark wrote:
> > hi,
> >
> > i want to display the five most recent rows in my table (there is a
> > timestamp), but sorted in ascending order.
> >
> > the problem is that when i call
> >
> > SELECT * FROM spam ORDER BY time ASC LIMIT 5
> >
> > it shows the first five entries in ascending order, but i want the last
> > five.
> >
> > needless to say, it works fine with descending order..
> >
> > thanks.
>
> SELECT * FROM (SELECT * FROM spam ORDER BY time DESC LIMIT 5) t1 ORDER
> BY time ASC;

SELECT * FROM (SELECT * FROM spam LEFT JOIN users USING (user_id) ORDER
BY time DESC LIMIT $view) t1 ORDER BY time ASC

wow..it works. that's so ugly :p
thank you so much!
but what does this "t1" mean?

"Mark" <mnbayazit [at] gmail.com> wrote in message
news:1156105682.598578.131630 [at] b28g2000cwb.googlegroups.com.. .
>
> strawberry wrote:
>> Mark wrote:
>> > hi,
>> >
>> > i want to display the five most recent rows in my table (there is a
>> > timestamp), but sorted in ascending order.
>> >
>> > the problem is that when i call
>> >
>> > SELECT * FROM spam ORDER BY time ASC LIMIT 5
>> >
>> > it shows the first five entries in ascending order, but i want the last
>> > five.
>> >
>> > needless to say, it works fine with descending order..
>> >
>> > thanks.
>>
>> SELECT * FROM (SELECT * FROM spam ORDER BY time DESC LIMIT 5) t1 ORDER
>> BY time ASC;
>
> SELECT * FROM (SELECT * FROM spam LEFT JOIN users USING (user_id) ORDER
> BY time DESC LIMIT $view) t1 ORDER BY time ASC
>
> wow..it works. that's so ugly :p
> thank you so much!
> but what does this "t1" mean?

It's just aliasing the return set and is required for syntax correctness I
believe.

Jim Langston wrote:
>> but what does this "t1" mean?
>
> It's just aliasing the return set and is required for syntax correctness I
> believe.

Right. Technically, it's called a "correlation name". It's required
when you use a subquery as a derived table in the FROM clause, because
otherwise how would you refer to columns from that derived table?

SELECT foo FROM table1, (SELECT foo FROM table2);

Does the above retrieve foo from table1 or foo from table2? To resolve
the ambiguity, we use correlation names:

SELECT t1.foo FROM table1 AS t1, (SELECT foo FROM table2) AS t2

Regards,
Bill K.

Bill Karwin wrote:
> Jim Langston wrote:
> >> but what does this "t1" mean?
> >
> > It's just aliasing the return set and is required for syntax correctness I
> > believe.
>
> Right. Technically, it's called a "correlation name". It's required
> when you use a subquery as a derived table in the FROM clause, because
> otherwise how would you refer to columns from that derived table?
>
> SELECT foo FROM table1, (SELECT foo FROM table2);
>
> Does the above retrieve foo from table1 or foo from table2? To resolve
> the ambiguity, we use correlation names:
>
> SELECT t1.foo FROM table1 AS t1, (SELECT foo FROM table2) AS t2
>
> Regards,
> Bill K.

i see. i thought it had to do with naming, but as far as i can see "t1"
was never referenced, so i was wondering why it was necessary. but i
guess for syntax's sake..