MSQL Query

by 9 replies
11
Change this query:

SELECT DISTINCT Skill FROM ratings WHERE User='$F'

to order by the count of each distinct value

For example if Skill returns the followng distinct values 2 - 5 - 7
and in the table there are (5)2's - (3)5's - (10)7's

the return order would be 7 - 2 - 5
#programming #msql #query
  • by heart (not checked) something like:

    select count(skill) as cnt from table GROUP BY skill ORDER BY cnt ASC
    • [ 1 ] Thanks
    • [2] replies
    • ORDER BY 1 DESC
      • [ 1 ] Thanks
    • Actually, there's no need for `count(skill)` explicitly, you can just as easily say `count(*)`. I would write the query like so, just for clarity:

      Code:
      select skill, count(*) as skillcount from tablename group by skill order by skillcount;
      Here's some real results from a test table I work with:

      Code:
      mysql> select status, count(*) as statuscount from core_job group by status order by statuscount desc;
+-------------+-------------+
| status      | statuscount |
+-------------+-------------+
| DONE        |           7 |
| ERROR       |           6 |
| DOWNLOADING |           5 |
| NEW         |           1 |
+-------------+-------------+
4 rows in set (0.02 sec)
      • [ 1 ] Thanks
  • I vaguely remember reading some time that using count(col) explicitly rather then * is faster.. however I do doubt that in this situation.. yours looks good and I see tested I just wrote it down passing by this thread
    • [1] reply
    • Could be, I have no idea. I'm no DBA
  • SELECT field
    FROM table
    GROUP BY field
    ORDER BY COUNT(field) DESC
  • SELECT DISTINCT skill, COUNT( * ) AS skillcount
    FROM rating
    WHERE userid = '1'
    GROUP BY skill
    ORDER BY skillcount DESC

    Hope this helps
    • [1] reply
Join the discussion

Next Topics on Trending Feed