SQL??

[Stevie]

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?

[Buzby]

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.

[Stevie]

When I use that SQL statement, it complains and seems to be waiting for me to pass in a value for Total.

Any ideas?

[Buzby]

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.

[Stevie]

Cheers, works a treat.

[Chris]

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.

SELECT TOP 5 Work_Date FROM Work_History WHERE Work_Date <> "";

[Buzby]

TOP [x] doesn't work in all flavours of SQL. If only it did!