[RESOLVED] ORDER BY 'column number' problem

[stubarny]

ORDER BY 'column number' problem

Hi everyone,

I'm looking to change the following code so that it sorts by a column number rather than a column name:

select * from (
SELECT title, body, post_timestamp FROM ads_live WHERE MATCH(title, body) AGAINST ('$_GET[q]' IN BOOLEAN MODE) ORDER BY post_timestamp DESC LIMIT 1,2) as a order by a.a.post_timestamp ASC

So I've tried the following which doesn't work:

select * from (
SELECT title, body, post_timestamp FROM ads_live WHERE MATCH(title, body) AGAINST ('$_GET[q]' IN BOOLEAN MODE) ORDER BY 2 DESC LIMIT 1,2) as a order by a.a.2 ASC

In testing i've found that it's the "a.a.2" part that is failing.

Thanks for any help you can give!

Stu

[Roger_Ramjet]

just make it 2, it is the order of the selected columns regardless of origin so you do not need to specify database or table, just position in resultset.

[stubarny]

Many thanks 😃

Stu