MSQL Query

by FirstSocialApps

Posted: 13 years ago 9 replies

PROGRAMMING

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

SteveSRS 13 years ago

by heart (not checked) something like:

select count(skill) as cnt from table GROUP BY skill ORDER BY cnt ASC
- [ 1 ] Thanks
- 2 replies
Signature

Recurring Proven SEO Business Model Rent Out Your (Local) Sites and YT vids

Give you AD Funnel Super Power!
ADConnect.io - Lower CPC. Sky-Rocket Conversions!!
{{ DiscussionBoard.errors[7641936].message }}
- KirkMcD 13 years ago
  
  Originally Posted by SteveSRS
  
  ORDER BY cnt ASC
  
  ORDER BY 1 DESC
  
  [ 1 ] Thanks
  
  {{ DiscussionBoard.errors[7642148].message }}
- wayfarer 13 years ago
  
  Originally Posted by SteveSRS
  
  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)
  
  [ 1 ] Thanks
  
  Signature
  I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
  
  {{ DiscussionBoard.errors[7642489].message }}
SteveSRS 13 years ago

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
- Thanks
- 1 reply
Signature

Recurring Proven SEO Business Model Rent Out Your (Local) Sites and YT vids

Give you AD Funnel Super Power!
ADConnect.io - Lower CPC. Sky-Rocket Conversions!!
{{ DiscussionBoard.errors[7646487].message }}
- wayfarer 13 years ago
  
  Originally Posted by SteveSRS
  
  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
  
  Thanks
  
  Signature
  I build web things, server things. I help build the startup Veenome. | Remote Programming Jobs
  
  {{ DiscussionBoard.errors[7647103].message }}
abraham26 13 years ago

SELECT field
FROM table
GROUP BY field
ORDER BY COUNT(field) DESC
- Thanks
{{ DiscussionBoard.errors[7647213].message }}
weaveronline 13 years ago

SELECT DISTINCT skill, COUNT( * ) AS skillcount
FROM rating
WHERE userid = '1'
GROUP BY skill
ORDER BY skillcount DESC

Hope this helps
- Thanks
- 1 reply
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 }}
- Don Art 13 years ago
  
  always use count(*) - much faster
  
  COUNT(*) vs COUNT(col) - MySQL Performance Blog
  
  [ 1 ] Thanks
  
  1 reply
  
  {{ DiscussionBoard.errors[7666842].message }}
  
  windfinder 13 years ago
  
  Use COUNT(col_name) if you want to omitt null values from the count.
  Otherwise COUNT(*) is the way to go.
  
  Thanks
  
  Signature
  Bitcoins without mining
  
  {{ DiscussionBoard.errors[7668295].message }}