MySQL - Choose specified search's maximum value without LIMIT or JOIN

Question

I'm very newbie with MySQL. I have found several ideas and codes here at Stack Overflow to get a specified maximum value. All of them was totally different, and I can't decide which is the best way. I can't use GREATEST or LEAST, as I know it's not working with has PK and/or has FK multiple tables. (On my final exam I have to use FK and PK too.)

Here is my own code:

SELECT Column
FROM table
WHERE Column =
(
    SELECT MAX(Column)
    FROM table
    WHERE Value = true
)

I found a lazy version to get the maximum value:

SELECT ColumnName
FROM table
WHERE Value = true
ORDER BY ColumnName DESC
LIMIT 1

As I know this first makes an order, which is more time than check all data once and get what is the smallest value.

I also found this sentence-like method which using JOIN:

select
 f.*
from
 foo f
inner join
(
 select min(id) as id from foo
) m on m.id = f.id;

But now I don't have multiple tables and I don't think I have to make a (temporary?) sub-table because of one query from one table.

Here is my task: Make a query which gas state of matter is discovered the last.

My only 1 table's data with columns: http://pastebin.com/raw/82zZ0rh2

If an element is gas state of matter then the Gas column's row's value is 1, else it's 0.

My example code is deal with it like this:

SELECT DiscoverYear, ElementName
FROM discoveries
WHERE DiscoverYear =
(
    SELECT MAX(DiscoverYear)
    FROM discoveries
    WHERE Gas = 1
)

Please imagine tons of data. What is the best way to get the maximum?

All the methods are quite reasonable with the right indexing structure. — Gordon Linoff, Feb 12 '17 at 14:19

score 1 · Accepted Answer · answered Feb 12 '17 at 14:47

I think your question is this: Which gaseous element was discovered most recently?

Your example solution is correct.

SELECT DiscoverYear, ElementName
FROM discoveries
WHERE DiscoverYear =
(
    SELECT MAX(DiscoverYear)
    FROM discoveries
    WHERE Gas = 1
)

If it happens that two elements were discovered in the same year, you'll get both, which is a reasonable outcome.

You need SQL indexes to make this work with lots of data. Notice that the number of elements in the periodic table is a very small dataset and not worth your time to optimize.

The inner query SELECT MAX(DiscoverYear) FROM discoveries WHERE Gas = 1 can be optimized by a compound index on (Gas, DiscoverYear). You create that with this command.

ALTER TABLE discoveries ADD INDEX state_year (Gas, DiscoverYear)

MySQL can satisfy this query using a loose index scan which is very efficient.

The outer query can be made faster by an index on DiscoverYear.

ALTER TABLE discoveries ADD INDEX state_year (DiscoverYear)

Some might suggest a compound index on (DiscoverYear, ElementName). However, in your problem, your outer query only looks up one or a very few items.

Read this for lots of ways of approaching such problems. http://use-the-index-luke/ One thing to avoid: putting lots of indexes on your tables just in case you need them.

Thanks for your fast answer. I didn't think about more outcomes in the same year, I didn't even know it's in my solution. You explained everything and also good links for improve my and may others knowledge. It's important for me because I have to make an oral final exam too. Thanks again! — Charusso, Feb 12 '17 at 16:28
You're welcome. I have a soft spot in my heart for chemists learning computing; I was one once. — O. Jones, Feb 12 '17 at 19:07

MySQL - Choose specified search's maximum value without LIMIT or JOIN

1 Answers1