CODEWITHSUNDEEP
mysqlsqlselectcountdistinct
RockFeller Dumbstep
RockFeller Dumbstep

Reputation: 51

Select distinct rows by count in descending order

I have a table hastags

id|tweet_id|  tag   |time
1   1       hell   
2   2       hellyeah    
3   3       hell
4   4       stackoverflow
5   5       hell   
6   6       hellyeah    
7   7       bellrings
8   7       nomorehell
8   7       nomorehell

I want to select the count of most used tags above a specific time. Now if i limit my query to 3 rows i should get

|tag    | count|
hell        3
hellyeah    2
nomorehell  2

How can i achieve this any help ?

Upvotes: 0

Views: 2233

Answers (3)

Puneet Bharti
Puneet Bharti

Reputation: 126

Considering start time and end time is given then this query will work.

SELECT tag, count(tag) FROM hastags where (start <= end and :time >= start and :time <= end) or
  (end < start and (:time <= end or :time >= start)) GROUP BY tag;

Upvotes: 0

juergen d
juergen d

Reputation: 204854

I chose 2014-12-29 as an example of a specific time

select tag, 
       sum(`time` > '2014-12-29') as sum_greater,
       sum(`time` < '2014-12-29') as sum_smaller
from hashtags
group by tag
order by sum_greater desc, sum_smaller desc
limit 3

Upvotes: 0

Dewstar
Dewstar

Reputation: 211

SELECT tag, count(*) as anz FROM hastags GROUP BY tag ORDER BY anz DESC

Upvotes: 1

Related Questions