I need a way of selecting distinct rows on a per day basis. Take the snapshop of my table for example.
Code: Select all
row_id | name | date
--------------------------------------
1 | Joey | 1234567890
2 | Joey | 1234567910
3 | Joey | 1234567950
4 | Joey | 1233567890
5 | Joey | 1233567980
6 | Joey | 1233567990
7 | Joey | 1233557890
8 | Joey | 1233567890
9 | Joey | 1233567890
10 | Andy | 1233568890
11 | Andy | 1233567890
12 | Andy | 1233566890
13 | Andy | 1233565890
14 | Andy | 1233564890We'd have 5 results returned. As following:
Code: Select all
Name | Day | Num_of_occurrences
-----------------------------------------------
Joey | 1st Jan | 4
Andy | 1st Jan | 3
Joey | 2nd Jan | 3
Andy | 2nd Jan | 2
Joey | 3rd Jan | 2
Code: Select all
SELECT DISTINCT name,date,COUNT(*) AS Num_of_occurrences FROM TABLE GROUP BY dateIt's quite a difficult problem to explain, hope someone can help.