Add if else after order by clause in mysql

Question

Trying to add if else condition after order by clause for adding limit. I have to include limit as per condition. I am trying a query but it generates error. Can't understand what's wrong with it please help !!

My Query :

select
    date_format(CONVERT_TZ(session_start, @@session.time_zone, '+05:30'),'%d %b, %y - %h:%i %p') session_start
FROM user_track
WHERE user_track.id='xyz123'
ORDER BY user_track.Sno DESC
if(count(session_start) > 1,(limit 1,1),(limit 1))

Possible duplicate https://stackoverflow.com/questions/8763310/how-do-write-if-else-statement-in-a-mysql-query — , Jun 24 '17 at 07:57

score 0 · Answer 1 · answered Jun 24 '17 at 08:01

MySQL (and any other RDBMS) does not allow logic of this sort in the LIMIT clause. But one workaround might be to use a row number variable to selectively choose either the first record, or the first record offset by one, i.e. the second record:

SET @row_number = 0;

SELECT t.session_start
FROM
(
    SELECT
        date_format(CONVERT_TZ(session_start, @@session.time_zone, '+05:30'),'%d %b, %y - %h:%i %p') session_start,
        (@row_number:=@row_number + 1) AS rn
    FROM user_track
    WHERE id = 'xyz123'
    ORDER BY Sno DESC
) t
WHERE
    t.rn = CASE WHEN (SELECT COUNT(session_start) FROM user_track
                      WHERE id = 'xyz123') > 1
                THEN 2 ELSE 1 END

Note that we needed to move the count to a separate subquery in the WHERE clause. We can't compute it in your main query, which is not doing an aggregation.

Add if else after order by clause in mysql

1 Answers1