ORDER BY l.linknum DESC limit 5 ?

MySQL & MariaDB Other MySQL® Questions
1

Hi All,

I’m attempting to combine two queries in order to improve performance. I have created the following SQL which works but doesn’t do the ORDER BY section correctly - it doesn’t extract the 5 most recent linknum’s

select l.linknum, l.catagory, l.name, l.link, l.views, count(r.linknum) as RATECOUNT, sum(r.rating)from links l, ratings rwhere l.linknum=r.linknum and l.catagory=64 group by l.linknum, l.catagory order by l.linknum desc limit 5;

Any idea what is wrong with this? The following is the original single query where the ORDER BY works:

select linknum, catagory, name, link, date_entered from links where catagory in (64) order by linknum desc limit 5;

Many thanks

p

2

I think I’ve spotted my error - there may not be matching entries in the ‘rating’ table…

not sure how to code around this - any advice would be appreciated )

Many thanks,

p.

3

Thought I found the answer but it doesn’t look like it’s support by my version of MySQL:

mysql> select l.linknum, l.catagory, l.name, l.link, l.views, count(r.linknum) as RATECOUNT, sum(r.rating) → from links l, ratings r → where l.linknum in (select linknum from links where catagory=64 order by linknum desc limit 5) → group by l.linknum;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect…Connection id: 2673Current database: xxxxERROR 1235 (42000): This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

(