Order By Question

[snowboardr]

How could I order 20 records, by greatest number of two columns? Not the sum of the two columns, but order then my be the highest number in each.

It only lists the first column from greatest to lowest but the second one is not in any order...

ORDER by user_tposts DESC, user_treply DESC LIMIT 0,20


www.vzio.com
ASP WEB DEVELOPMENT

 

[swampBoogie]

ORDER by 
case when user_tposts > user_treply then
  user_tposts else user_treply end DESC 
LIMIT 0,20

 

[snowboardr]

Thanks swampboogie, but that didn't change any order in my query, it produced the same order as my current query ordering by the sum of user_tposts and user_treply, for some reason.

rsTopMember.Source = "SELECT userID,user_name,user_tposts,user_flame,user_treply,SUM(user_tposts+user_treply) AS TotalOverAllPosts FROM users GROUP BY userID ORDER by TotalOverAllPosts DESC LIMIT 0,20"

rsTopMember.Source = "SELECT userID,user_name,user_tposts,user_flame,user_treply,SUM(user_tposts+user_treply) AS TotalOverAllPosts FROM users GROUP BY userID ORDER by case when user_tposts > user_treply then user_tposts else user_treply end DESC LIMIT 0,20"

- Jason

 

[swampBoogie]

Oh you have a group by. Mysql does not handle this correctly. The query is not valid SQL.

What are you trying to accomplish?

Describe your tables, some sample data and the expected result.

 

[snowboardr]

user_tposts
holds how many topics started by the user

user_treply
holds how many replies the user has made to topics

TotalOverAllPosts
the total between the above two

I would like to order the data based on the highest number of both user_tposts and user_treply, not based on the total.

so like

100 99
77 88
55 44

etc

 

[swampBoogie]

Okay, in that case you shouldn't need a group and sum at all

SELECT userID,
       user_name,
       user_tposts,
       user_flame,
       user_treply,
       user_tposts+user_treply AS TotalOverAllPosts 
  FROM users 
  ORDER by case 
  when user_tposts > user_treply then user_tposts 
                                 else user_treply end 
 DESC LIMIT 0,20