Databases
StackOverflow2013
Postsdbo
POMySQL "ORDER BY" the amount of rows with the same value for a certain column?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL "ORDER BY" the amount of rows with the same value for a certain column?
    primarykey
    data
    text
    <p>I have a table called <code>trends_points</code>, this table has the following columns:</p> <ul> <li>id (the unique id of the row)</li> <li>userId (the id of the user that has entered this in the table)</li> <li>term (a word)</li> <li>time (a unix timestamp)</li> </ul> <p>Now, I'm trying to run a query on this table which will get the rows in a specific time frame ordered by how many times the column <code>term</code> appears in the table during the specific timeframe...So for example if the table has the following rows:</p> <pre><code>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 </code></pre> <p>I'd like the rows to come out ordered like this:</p> <pre><code>new year christmas twitter </code></pre> <p>This is because "new year" exists three times in the timeframe, "christmas" exists twice and "twitter" is only in one row.</p> <p>So far I've asummed it's a simple WHERE for the specific timeframe part of the query and a GROUP BY to stop the same term from coming up twice in the list.</p> <p>This makes the following query:</p> <pre><code>SELECT * FROM `trends_points` WHERE ( time &gt;= &lt;time-period_start&gt; AND time &lt;= &lt;time-period_end&gt; ) GROUP BY `term` </code></pre> <p>Does anyone know how I'd do the final part of the query? (Ordering the query's results by how many rows contain the same "term" column value..).</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USSimon
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL "ORDER BY" the amount of rows with the same value for a certain column?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL "ORDER BY" the amount of rows with the same value for a certain column?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POMySQL "ORDER BY" the amount of rows with the same value for a certain column?
      1. USevrimbakir
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload