CODEWITHSUNDEEP
mysqlsql
user2048211
user2048211

Reputation: 1

Using the ORDER BY clause in SQL

Is it possible to use a keyword that would order the results in my SELECT QUERY by the number of times they appear in the data table?

MySQL:

  SELECT * FROM interests ORDER BY (keyword)

The table values:

hiking
running
swimming
hiking
photography
swimming
hiking

If the Order in which they are returned is based on the frequency of each values occurence in the table it would be:

hiking
hiking
hiking
swimming
swimming
running
photography

If there is no one particular keyword, how can I achieve this with SQL?

Thanks

Upvotes: 0

Views: 87

Answers (3)

Bhaskar Bhatt
Bhaskar Bhatt

Reputation: 1467

    SELECT 
    t1.*,count(t1.keyword) 
    FROM 
    interests AS t1 INNER JOIN (
     SELECT 
            keyword,count(*) totalcount 
      FROM 
            interests GROUP BY keyword
      )t2 
ON t1.keyword=t2.keyword group by t1.keyword 
   ORDER BY t2.totalcount DESC

Sql Fiddle demo

Upvotes: 0

John Woo
John Woo

Reputation: 263693

SELECT  a.*
FROM    interests a
        INNER JOIN 
        (
            SELECT  keyword, COUNT(*) totalCount
            FROm    interests
            GROUP BY keyword
        ) b ON a.keyword = b.keyword
ORDER   BY b.totalCount DESC

Upvotes: 4

Marc B
Marc B

Reputation: 360572

SELECT COUNT(keyword) AS cnt, keyword
FROM interested
GROUP BY keyword
ORDER BY cnt

would give you the count of each keyword in ascending order. But you wouldn't get 3 hiking entries, 2swimming, etc... just hiking,3, swimming,2, etc...

Upvotes: 5

Related Questions