Rick Joe Rick Joe - 7 months ago 15
SQL Question

order by desc not shows in the correct order

I have a chat service, so:

select k.user, c.id, c.from, c.sent, c.message, c.recd from chat c
inner join cadastro k on c.from=k.id
where (`from`=? and `to`=?)
order by c.id
limit 50


I want to show the last 50 messages, if I send 1 to 100 it will show:

1
2
3
...
49
50


I want to show the last 50 messages like this:

50
51
...
100


if I put
order by c.id desc
it give me the order:

50
49
...
4
3
2
1


any ideas?

Answer
select * from
(
  select k.user, c.id, c.from, c.sent, c.message, c.recd from chat c
  inner join cadastro k on c.from=k.id
  where (`from`=? and `to`=?)
  order by c.id desc
  limit 50
) tmp
order by id asc