CODEWITHSUNDEEP
mysqlsql
Maruf Saidov
Maruf Saidov

Reputation: 43

ORDER BY in WHERE EXIST query works incorrectly

ORDER BY does not work with where exist

I have SQL generated by ORM, but it sorts data incorrectly

SELECT * FROM `users`
WHERE (
      EXISTS
(
  SELECT *
  FROM `passports`
  WHERE `users`.`id` = `passports`.`user_id`
  AND DATE_ADD(
       birth_date,
        INTERVAL YEAR(CURDATE())-YEAR(birth_date) + IF(DAYOFYEAR(CURDATE()) 
                                               > DAYOFYEAR(birth_date),1,0)
        YEAR)
     BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
  ORDER BY DATE_ADD(
     birth_date,
            INTERVAL YEAR(CURDATE())-YEAR(birth_date)
                     + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birth_date),1,0)
            YEAR
       );
)

ORDER BY does not work But subquery sorts correctly

    SELECT *
    FROM  passports
    WHERE  DATE_ADD(
        birth_date,
               INTERVAL YEAR(CURDATE())-YEAR(birth_date)
                     + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birth_date),1,0)
               YEAR
          )

         BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
  ORDER BY DATE_ADD(
            birth_date,
            INTERVAL YEAR(CURDATE())-YEAR(birth_date)
                     + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birth_date),1,0)
            YEAR
       );

What is wrong in my code, how to make it work

I get an unsorted array of users(first query), But the sorted array of their passports ordered by soonest birthdays(subquery), I expect the output of the sorted array of users according to subquery rule.

Upvotes: 2

Views: 79

Answers (2)

nacho
nacho

Reputation: 5396

It´s because you have the order by in the wrong place. In the subquery, the system orders the rows, but then in the outer query, they don´t get any order (in SQL, even if they are ordered, the SELECT doesn´t return the information in that order. You need to order by if you want an specific order). You should take out the order by:

SELECT * 
FROM `users`
WHERE EXISTS
        (SELECT *
         FROM `passports`
         WHERE `users`.`id` = `passports`.`user_id`
               AND DATE_ADD(birth_date,
               INTERVAL YEAR(CURDATE())-YEAR(birth_date) + IF(DAYOFYEAR(CURDATE()) 
                                                   > DAYOFYEAR(birth_date),1,0)
               YEAR)
         BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)    
         )
ORDER BY DATE_ADD(birth_date,
                INTERVAL YEAR(CURDATE())-YEAR(birth_date)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birth_date),1,0)
                YEAR)

EDIT

As you said, it doesn´t find the column birth_date because it is in the passports table. I would change all your query to use an inner join instead of EXISTS:

SELECT * 
FROM `users`
inner join `passports` on `users`.`id` = `passports`.`user_id`
WHERE DATE_ADD(birth_date,
               INTERVAL YEAR(CURDATE())-YEAR(birth_date) + IF(DAYOFYEAR(CURDATE()) 
                                                   > DAYOFYEAR(birth_date),1,0)
               YEAR)
         BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 7 DAY)
 ORDER BY DATE_ADD(birth_date,
                INTERVAL YEAR(CURDATE())-YEAR(birth_date)
                         + IF(DAYOFYEAR(CURDATE()) > DAYOFYEAR(birth_date),1,0)
                YEAR)

If you don´t need all the columns, in the SELECT * you will have to change the * by the names of the columns you need (users.id, passports.birth_date, etc)

Upvotes: 1

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30645

The main question is why do you need order by clause inside EXISTS? ORM is definitely generating wrong SQL. It may be better to open new question with your ORM code so that the issue would be worked out.

you cannot expect your output data to be sorted in this scenario. The subquery gets sorted inside exists not on the main query.

simple example 

select * from tb 
where exists (
  select x from tb2
  order by x
);

only subquery with tb2 is sorted, but since it is used in EXISTS clause there is no effect to the output

Upvotes: 0

Related Questions