find records one above minimum

[BubikolRamios]

Need to get for each master, detail, that is one above minimum
of auto_inc column within detail.

If it was minimum to find that I can do, but it is not.

master

id
1
2
3
...


detail

id auto_ic text
1 1 a
1 2 b
1 3 c
1 4 d
2 5 e
2 6 f
2 7 g
...

expected result:


1 2 b
2 6 f

[koko5]

Hi,I didn't understand are these 2 tables or not but this doesn't matter:


SELECT * FROM TableName A
WHERE auto_ic=(SELECT MIN(auto_ic)+1 FROM TableName B WHERE B.id=A.id)
ORDER BY id ASC;

Regards
:thumbsup:

[Old Pedant]

If that doesn't work, try this:

SELECT detail.*
FROM detail,
( SELECT id, MIN(auto_ic)+1 AS ic1
FROM detail
GROUP BY id ) AS X
WHERE detail.id = X.id
AND detail.auto_ic = X.ic1


Koko: Can you try breaking your one-line answers into multiple lines, for readability? I have to copy/past your answers into notepad to make them readable. And I *do* want to read them.

[BubikolRamios]

Second one much faster.

[koko5]

...
Koko: Can you try breaking your one-line answers into multiple lines, for readability? I have to copy/past your answers into notepad to make them readable. And I *do* want to read them.
Sorry, I didn't consider this-thanks!
Second one much faster.
Interesting, explain for variant with sub-query hints other: maybe because of mysql optimizer ?!?
Edit: I mean can someone tell the reason for that?

[Old Pedant]

I think it is because MySQL can do the inner SELECT
SELECT id, MIN(auto_ic)+1 AS ic1
FROM detail
GROUP BY id

all in one very fast operation. But a lot of it probably depends on what fields are indexed.

If there is a UNIQUE KEY(id, auto_ic) on that table, then of course that inner SELECT will be lightning fast, all done in the key without using the table at all.

But try an EXPLAIN on both to see what it says.