What's the difference between "select max" and "select.. where column=max"?

Question

I have two queries:

SELECT id, max(index) from table;

and

SELECT id, index from table where index=(select max(index) from table);

Is there any difference between the two?

I ran the code in MySQL Query Browser and found that the results are the same. But I just feel that there should be a difference. If there is a diff, mind to attach a simple scenario?

Edit: the index is not unique.

Edit: You might say that my first query is missing group by; but it is actually working in mysql. I just want to know what's the diff between the two, not the diff between first query with group by and the second one.

Answer 1

The first one should return all table records with the same max(index) value on each.

id_1    100
id_2    100
id_3    100

etc.

The second one should only return one record with the maximum index value (provided your index field is unique within this table). If it's not, then the results may indeed be the same.

id_3    100

ADDED: For the case when [index] is not unique:

• The first query will return ALL table rows, for each two fields, the [id] and the same maximum value of [index].
• The second query will return ALL table rows that have the same maximum [index] value on them. This records subset will be less than or equal to ALL records subset.

Answer 2

I am not an expert on MySQL (see above). However, my reading of this is that if the max index is unique, then both queries will return the same unique id and index. However, if the max index appears for multiple rows, then the first query will return the max index and one indeterminate id. The second query will return as many rows as have the max index, with each id appearing for a row.

EDIT: Removed part of answer after reading APC's answer and realising what I'd written was incorrect.

Answer 3

Like Ruffles I am not an expert on MySQL, so I don't understand its behaviour when an aggregating function is used without an explicit GROUP BY. NewInTown's examples look odd to my mind.

But I would definitely expect the use of GROUP BY in the first query to produce a different resultset from the second. Certainly it does in Oracle.

SQL> select pk, max(idx)
  2  from whatever
  3  group by pk
  4  /

        PK   MAX(IDX)
---------- ----------
         1         10
         2         50
         3        100

SQL> select pk, idx
  2  from whatever
  3  where idx in ( select max(idx) from whatever)
  4  /

        PK        IDX
---------- ----------
         3        100

SQL>

The only time the results would be identical would be when all the records share the same value for IDX:

SQL> select pk, max(idx)
  2  from whatever
  3  group by pk
  4  /

        PK   MAX(IDX)
---------- ----------
         1        100
         2        100
         3        100

SQL> select pk, idx
  2  from whatever
  3  where idx in ( select max(idx) from whatever)
  4  /

        PK        IDX
---------- ----------
         1        100
         2        100
         3        100

SQL>

Answer 4

SELECT id, max(index) from table;

As has been pointed out, using this group function means you will have to group by id. If you missed out id from the select, your statement would return the max value from the index column across the whole table. If you include your group by, the results would be a row for each id and the max index for each id.

Your second query:

SELECT id, index from table where index=(select max(index) from table);

The subquery will select the max index from the whole table, then the outer query will return the id and index on any row that has the same index as the maximum.

In your case the results may be the same but the queries are very different.