I have a table called Work_History and in it a field called Work_Date.
How can I get hold of the Work_Date which occurs most often? :confused:
Printable View
I have a table called Work_History and in it a field called Work_Date.
How can I get hold of the Work_Date which occurs most often? :confused:
SELECT COUNT(Work_Date) AS Total, Work_Date FROM
Work_History GROUP BY Work_Date
ORDER BY Total DESC
- the most common work_date would appear at the top of the returned recordset.
When I use that SQL statement, it complains and seems to be waiting for me to pass in a value for Total.
Any ideas?
Just do
SELECT COUNT(Work_Date), Work_Date
FROM Work_History GROUP BY Work_Date
ORDER BY COUNT(Work_Date) DESC
The Total bit was just to make it look tidier.
Cheers, works a treat. :D
You also use the SELECT...TOP n sql statement as well to perform you task.
Where n is occurs most offen Work_Date in your databse. Exmaple, over here I put 5 that mean I wish to retrieve the top5 Work_Date that occurs most offen.
Code:SELECT TOP 5 Work_Date FROM Work_History WHERE Work_Date <> "";
TOP [x] doesn't work in all flavours of SQL. If only it did!