CODEWITHSUNDEEP
phpmysqlsqlsorting
jas7457
jas7457

Reputation: 1782

How to sort MySQL by two columns?

I made a music blog with a few friends and I made a MySQL table to keep track of post views. I have one column to keep track of admin views and one column keep track of user views for each post.

I am making a page to easily view and sort by most admin views, most user views, and total views. I did not make a column for "total views" in my MySQL database (To calculate, I just add the admin views to the user views. Plain and simple).

I can easily sort by admin views by querying SELECT * FROM log ORDER BY ADMIN DESC and the same for user views, but is there a simple way to do something like SELECT * FROM log ORDER BY ADMIN + USER DESC? If not, I will have to mess with my code to provide my own sorting algorithm.

Thanks ahead of time.

Upvotes: 1

Views: 197

Answers (2)

showdev
showdev

Reputation: 29168

If you're going to use the total views for anything besides sorting, I suggest:

SELECT *,(`admin`+`user`) as `total` FROM `log` ORDER BY `total` DESC

This will add a "total" field to your result which you can display or use in calculations.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

The query is exactly as you put it:

SELECT  *
FROM    log
ORDER BY
        admin + user DESC

If you created a separate column for total views and indexed it, you could use it in a query:

SELECT  *
FROM    log
ORDER BY
        total_views DESC
LIMIT   10

which would improve the SELECT speed for limited queries. The way you do it, the whole table should be scanned and ordered, even if you only select top 10 comments.

Upvotes: 5

Related Questions