Christian Möller Christian Möller - 1 month ago 7
MySQL Question

MYSQL How to select a row where a field is minimum inside another select

I have a table "ab_ws_zpt" with the columns: "WS_ID", "ZPT_ID", "abstand".

Data of "ab_ws_zpt" This is just a short abstract from the entire data. WS_ID goes up to 43 and entirely there are 600.000 ZPT_ID´s.

I try to select the WS_ID from the row with the minimum abstand in a specific select of a ZPT_ID.

It works when I just select the minimum abstand from the entire table "ab_ws_zpt" with:

SELECT
WS_ID, ZPT_ID, abstand
FROM
ab_ws_zpt
WHERE
abstand = (SELECT MIN(abstand) from ab_ws_zpt);


which brings the result of: Result of select

Also it works when I just select all WS_ID´s of one special ZPT_ID with:

SELECT
WS_ID, ZPT_ID, abstand
FROM
ab_ws_zpt
WHERE
ZPT_ID = '70000003';


Which results all 43 WS_ID for the ZPT_ID 70000003.

Now I tried to combine both statements into:

SELECT
WS_ID, ZPT_ID, abstand
FROM
ab_ws_zpt
WHERE
abstand = (SELECT
MIN(abstand)
FROM
(SELECT
WS_ID, b.ZPT_ID, abstand
FROM
ab_ws_zpt
WHERE
ZPT_ID = '70000003'));


And the database returns the errorcode 1248: Every derived table must have its own alias.

I tried to give the tables different alias in both selects, but the error keeps resulting. I hope someone can help me and correct my error or show me another way to get what i want.

This is my first question on here, so I´m sorry if my explanations are missleading. I tried my best to make it easy to understand.

Answer

Please give this a try:

SELECT 
    WS_ID, ZPT_ID, abstand
FROM
    ab_ws_zpt
WHERE
ZPT_ID = '70000003'
ORDER BY abstand ASC
LIMIT 1;
Comments