SQL Statement Help Please

zitman · #1 (**permalink**) 04-09-04, 08:25

have a table of statistical information which can contain zero or entries for a given day. I would like a single statement to find which day has the most entries on it.

The following statement will list the days and the number of entries for that day.

SELECT Date, MAX(COUNT(ID)) FROM Stats WHERE CompanyD=2 GROUP BY Date

Is there a way I can modify this statement so it returns a single row with the Date and the number of rows for that Date, but only for the Date with the most rows.

Hope thats clear.

Z

r937 · #2 (**permalink**) 04-09-04, 10:23

Code:

select `Date`
     , count(ID) as rows
  from Stats 
 where CompanyD = 2 
group 
    by `Date`
order
    by rows desc
 limit 1

tip: don't use a reserved word like Date for a column name, you are only asking for syntax errors

zitman · #3 (**permalink**) 04-11-04, 17:09

Many thanks.

It is actually quite simple really isn't it.

At least it is when you know how. Again thanks

Z

Internet Services

Web Development

Digital Marketing

Consumer Tech