CODEWITHSUNDEEP
mysqlgroup-bysql-order-by
Redandwhite
Redandwhite

Reputation: 2549

How do I order by the number of repetitions, then by date in MySQL?

I have the following SQL query.

SELECT p.`id`, p.`data`, p.`title`, p.`creation_date`, t.`name` AS 'tag'

    FROM `post_tag` pt, `post` p, `tag` t 

    WHERE p.`id` = pt.`post` 
        AND pt.`tag` = t.`id` 
        AND p.`author` <> '1'
        AND (t.`name` = 'Programming' 
            OR t.`name` = 'Photography' 
            OR t.`name` = '[...]') 

    ORDER BY p.`creation_date`, t.`name` DESC

And my resulting table looks like this:

sql result

As I've highlighted in the screenshot, rows with ID 76 and 77 are repeated. In my application's case, that is a measure of "popularity". I want to be able to:

  1. Group rows where "data" is repeated into one row (I know I can do this with GROUP BY)
  2. Order the rows by "popularity" first, then by "creation_date". So, if there are 3 rows with the same "data", the I want that to be returned first, followed by rows with a count of 2, etc.

Upvotes: 1

Views: 706

Answers (1)

jspcal
jspcal

Reputation: 51904

... group by p.data order by count(*) desc, p.creation_date, t.name desc

Upvotes: 1

Related Questions