Geek Answers Handbook

MySQL "ORDER BY" the number of rows with the same value for a particular column?

I have a table called trends_points , this table has the following columns:

  • id (unique identifier for the string)
  • userId (identifier of the user who entered this into the table)
  • term (word)
  • time (unix timestamp)

Now I'm trying to run a query in this table that will receive rows in a certain time interval, ordered by how many times the term column appears in the table during a certain timeframe ... So for example, if the table has the following rows:

 id | userId | term | time ------------------------------------ 1 28 new year 1262231638 2 37 new year 1262231658 3 1 christmas 1262231666 4 34 new year 1262231665 5 12 christmas 1262231667 6 52 twitter 1262231669

I would like the lines to go like this:

 new year christmas twitter

This is because “New Year” exists three times in the timeframe, “Christmas” exists twice, and “Twitter” - only on one line.

So far, I have requested this simply WHERE for the specific part of the time request and GROUP BY so that the same term does not return twice in the list.

This makes the following request:

 SELECT * FROM `trends_points` WHERE ( time >= <time-period_start> AND time <= <time-period_end> ) GROUP BY `term`

Does anyone know how I will do the final part of the request? (Ordering query results for how many rows contains the same value for the "term" .. column).

+4
source share
4 answers

Using:

  SELECT tp.term, COUNT(*) 'term_count' FROM TREND_POINTS tp WHERE tp.time BETWEEN <time-period_start> AND <time-period_end> GROUP BY tp.term ORDER BY term_count DESC, tp.term

See this question about why using BETWEEN vs with> = / <= operators.

Keep in mind that there may be connections - the default order is reduced to alphabetical closure by urgent value when this happens, but there may be other criteria.

In addition, if you want to further limit the number of returned lines / terms, you can add a LIMIT clause to the end of the query. For example, this query will return the top five terms:

  SELECT tp.term, COUNT(*) 'term_count' FROM TREND_POINTS tp WHERE tp.time BETWEEN <time-period_start> AND <time-period_end> GROUP BY tp.term ORDER BY term_count DESC, tp.term LIMIT 5
+11
source

Quick response:

 SELECT term, count(*) as thecount FROM mytable WHERE (...) GROUP BY term ORDER BY thecount DESC
+4
source 
 SELECT t.term FROM trend_points t WHERE t.time >= <time-period_start> AND t.time <= <time-period_end> ORDER BY COUNT(t.term) DESC GROUP BY t.term
+2
source

COUNT() will give you the number of rows in the group, so just order it.

 SELECT * FROM `trends_points` WHERE ( `time` >= <time-period_start> AND `time` <= <time-period_end> ) ORDER BY COUNT(`term`) DESC GROUP BY `term`
+1
source

More articles:


All Articles