MySQL Forums
Forum List  »  General

Can somebody help me understand why these two quey's do not return the same amount of rows
Posted by: Chiel Bos
Date: October 18, 2022 03:28AM

I need help on understanding why some rows are missing in de results of the second query compared the the results of the first query. I am breaking my head on this for days now.

SELECT T1.`orderID`, MAX( T1.`prio`) AS `prio`
FROM `planning` AS T1
WHERE T1.`done` < 100
AND T1.`prio` = T1.`prio` # useless, but to make it look more equal
GROUP BY T1.`orderID`
ORDER BY `prio` DESC;
# returns 79 rows

SELECT T1.`orderID`, T1.`prio`
FROM `planning` AS T1
WHERE T1.`done` < 100 # untill here will select 79 rows as we see in the first query
AND T1.`prio` = (
SELECT MAX( T2.`prio`)
FROM `planning` AS T2
WHERE T1.`orderID` = T2.`orderID` # from here we seem to miss 17 rows, but T1 = T2 so why.
)
GROUP BY T1.`orderID`
ORDER BY `prio` DESC;
# returns 62 rows, all 62 also apear in the first query.

To my understanding the second query only differs on comparing T1.`prio` and T2.`prio` where T1 and T2 are the same tables so this can't exclude any rows. How come different amount of rows are returned. I need to understand this to know if my database is ok and to continue my project.

Options: ReplyQuote


Subject
Written By
Posted
Can somebody help me understand why these two quey's do not return the same amount of rows
October 18, 2022 03:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.