11 {{ upvoteCount | shortNum }}
11 {{ upvoteCount | shortNum }}

MSQL Query

by FirstSocialApps
9 replies
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
#msql #query
  • Profile picture of the author SteveSRS
    by heart (not checked) something like:

    select count(skill) as cnt from table GROUP BY skill ORDER BY cnt ASC
    Signature
    {{ DiscussionBoard.errors[7641936].message }}
    • Profile picture of the author KirkMcD
      Originally Posted by SteveSRS View Post

      ORDER BY cnt ASC
      ORDER BY 1 DESC
      {{ DiscussionBoard.errors[7642148].message }}
    • Profile picture of the author wayfarer
      Originally Posted by SteveSRS View Post

      by heart (not checked) something like:

      select count(skill) as cnt from table GROUP BY skill ORDER BY cnt ASC
      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)
      Signature
      I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
      {{ DiscussionBoard.errors[7642489].message }}
  • Profile picture of the author SteveSRS
    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
    Signature
    {{ DiscussionBoard.errors[7646487].message }}
    • Profile picture of the author wayfarer
      Originally Posted by SteveSRS View Post

      I vaguely remember reading some time that using count(col) explicitly rather then * is faster..
      Could be, I have no idea. I'm no DBA
      Signature
      I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
      {{ DiscussionBoard.errors[7647103].message }}
  • Profile picture of the author abraham26
    SELECT field
    FROM table
    GROUP BY field
    ORDER BY COUNT(field) DESC
    {{ DiscussionBoard.errors[7647213].message }}
  • Profile picture of the author weaveronline
    SELECT DISTINCT skill, COUNT( * ) AS skillcount
    FROM rating
    WHERE userid = '1'
    GROUP BY skill
    ORDER BY skillcount DESC

    Hope this helps
    Signature

    Thanks & Regards,
    Reach us at dukeduke600@gmail.com.
    Web Design| Logo Design | Banner Design | Web Development | Mobile Applications [iPhone/iPad/Android/Windows Phone]

    {{ DiscussionBoard.errors[7665374].message }}

Trending Topics