Title: SQL : I search the good query with select max() and select count()
Tags: mysql;sql;select;count;max
Question: I have a table named arrived (this table is simple, and it just saves the users that arrive at school at such day and at such hour) with the following data :

```id  name    day         hour
1   Alice   Monday      11
2   Alice   Monday      13
3   Alice   Tuesday     11
4   Céline  Wednesday   14
5   Céline  Wednesday   13
6   Céline  Thursday    14
7   Maud    Friday      15
8   Maud    Saturday    15
9   Maud    Saturday    16
```

Now, I search the good query that find for each user : the most frequent day and the most frequent hour, that is say, the result of the query must return this lines :

```Alice   Monday      11
Céline  Wednesday   14
Maud    Saturday    15
```

=> because :


Alice frequently arrives at Monday, and frequently at 11h
Céline frequently arrives at Wednesday, and frequently at 14h
Maud frequently arrives at Saturday, and frequently at 15h


My query is below, but it doesn't give me the good result :

```SELECT NAME,
       day,
       Max(count_hour)
FROM   (SELECT NAME,
               day,
               Count(hour) AS count_hour
        FROM   arrived
        GROUP  BY NAME,
                  day) AS alias_table
GROUP  BY NAME 
```

Thank you, cordially.

Comment: If you can, instead of `day` and `hour`, just store `datetime`.

Here is the accepted answer: Not sure is this the right way to do it but should work!

```SELECT *
FROM   arrived A
WHERE  hour = (SELECT hour
               FROM   arrived B
               WHERE  a.NAME = b.NAME
               GROUP  BY b.hour,
                         b.NAME
               ORDER  BY Count(b.hour) DESC Limit 1)
       AND day = (SELECT day
                  FROM   arrived B
                  WHERE  a.NAME = b.NAME
                  GROUP  BY b.day,
                            b.NAME
                  ORDER  BY Count(b.day) DESC Limit 1) 
```

SQLFIDDLE DEMO

Comment for this answer: This query will fail to return a row if the combination (max(day),max(hour)) does not exist, see http://sqlfiddle.com/#!2/489a2c/1/0
Comment for this answer: This is a modified version returning the correct result: http://sqlfiddle.com/#!2/489a2c/3/0
Comment for this answer: Thanks for your reply. But I just have one table named "arrived". Why do you use two tables ?
Comment for this answer: Thank you very much, it works ! effectively, you use A and B as alias tables. Thank you very much !!!
Comment for this answer: @totoaussi - Even i have used only one table named **Yourtable**. Now edited the name to **arrived**
Comment for this answer: @totoaussi - Glad it helped you, Cheers :)

Here is another answer: Try the ```greatest(day, hour)``` when selecting columns. You can also try ```greatest(max(day), max(hour))```

Cheers!

Comment for this answer: Even if greatest is not appropriate, thank you for your reply.

{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 2, 'CommentCount': 1, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2015:00:02/14/15', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<mysql><sql><select><count><max>', 'Title': 'SQL : I search the good query with select max() and select count()', 'ViewCount': 99, 'id': 28517087, 'user_AboutMe': None, 'user_AccountId': 3409033.0, 'user_DisplayName': 'totoaussi', 'user_Reputation': 683.0, 'user_Views': 65.0, 'user_id': 4566802.0}}