MySQL group by issue

Question

I'm having a strange problem with MySQL and would like to see if the community has any thoughts:

I have a table 'tbl' that contains

 ____________
| id | sdate  |

And I'm trying to execute this query:

select id, max(sdate) as sd from tbl where id in(123) group by id;

This returns no results. However, this query:

select id, sdate from tbl where id in(123);

Returns many results with id's and dates.

Why would the top query fail to produce results?

Answer 1

So IDs in this table aren't distinct, right? For example, it could be a list of questions here on StackOverflow with a viewed date, and each question ID could appear multiple times in the results. Otherwise, if the IDs are always unique then there's no point in doing a GROUP BY on them. When you're restricting the results to a single ID you don't technically need the GROUP BY clause since MAX() is an aggregate function that will return a single row.

What's the datatype of sdate? int/datetime?

It's perfectly fine to supply a single ID to an IN() clause; it just can't be blank: IN().

Is it possible to provide the output of "DESCRIBE tbl;" and a few example rows?

Answer 2

Turns out the index was corrupt. Running the following solved the issue:

REPAIR TABLE tbl;