On Friday at work, I was working through some code I was fixing with a coworker in a code review. We had a table that had a foreign key into our users table, and there was a unique constraint on this table saying that the combination of USER_ID and MOBILE_NUMBER needed to be unique (so any individual user could only have one row in the table per phone number). The old code was grabbing the first row that matched USER_ID and then updating the MOBILE_NUMBER field, and we were getting unique constraint errors when we wrote the row back to the DB because sometimes a user had two rows, one for mobile number A and one for mobile number B, and if they told us “use mobile number B” and the code was trying to update the row for mobile number A…

My fix was running a query that looked pretty much like this:

SELECT * FROM my_table WHERE user_id = ?

SELECT * FROM my_table WHERE user_id = ?

and then I was looping through the results. If I found a row that had the MOBILE_NUMBER I was going to be adding, I grabbed that row to update, otherwise I grabbed any old row to update.

My coworker said “wouldn’t it be nice if we could sort the rows based on the mobile number and get the row we want first?” My immediate response was “Man, this is the kind of problem I’d as my mom for help with…”

And then I had a flurry of thoughts: I had stopped asking my mom for help in 2018 because she started answering my questions with Oracle-only SQL answers, and I was stuck in MySQL hell. When I complained that I needed a generic SQL solution and not something that used an Oracle-only feature, she simply replied “Just use Oracle.” At the time, I realized that my mom was 74, and I was 51, and I needed to just pull up my big boy pants and let her work on her own work and not make her do mine (she was a full-time employee doing Oracle DB work until she died this past January).

But then I realized I WAS USING ORACLE IN THIS JOB! My mom really could help me. So I dug deep down inside and asked my mom for help.

Immediately, I had an idea. “Let me try something” I told my coworker.

I then tried to run this

SELECT * FROM my_table WHERE user_id = ?
ORDER BY IF(mobile_number = ?, 1, 0) DESC

SELECT * FROM my_table WHERE user_id = ?
ORDER BY IF(mobile_number = ?, 1, 0) DESC

and my Oracle client immediately complained that it had no clue what IF() meant. I heard my mom’s voice gently saying that I was using Oracle, and IF() was MySQL. So I Googled oracle sql ternary operator. The first result was a link to a Stack Overflow article that basically pointed me straight at CASE WHEN.

SELECT * FROM my_table WHERE user_id = ?
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC

SELECT * FROM my_table WHERE user_id = ?
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC

Boom! It worked. I reported this back to my coworker, and his response was “TIL that you can use CASE WHEN in an ORDER BY clause.” I excitedly said “SAME! I didn’t know it would work until I tried it.” Then we talked about how, now that we were getting back the row we wanted first, we didn’t need ALL the rows, we could just take the first one. Back to my client and I tried

SELECT * FROM my_table WHERE user_id = ?
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC
LIMIT 1

SELECT * FROM my_table WHERE user_id = ?
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC
LIMIT 1

Womp-womp-womp. Apparently, LIMIT is not available in Oracle (though it is available in PostgreSQL). Back to Google, where I found that the Oracle-ese is either checking a ROWNUM column or using FETCH FIRST 1 ROWS ONLY. I then tried it out in my code, to see if I could put SQL comments in a query being sent from Perl…

SELECT * FROM my_table WHERE user_id = ?
-- sort the rows so if there's one matching the mobile number, it's first
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC
-- and only grab one row
FETCH FIRST 1 ROWS ONLY

SELECT * FROM my_table WHERE user_id = ?
-- sort the rows so if there's one matching the mobile number, it's first
ORDER BY CASE WHEN mobile_number = ? THEN 1 ELSE 0 END DESC
-- and only grab one row
FETCH FIRST 1 ROWS ONLY

And everything worked just fine.

I feel like my mom was prodding me along throughout the whole process. For just a few moments, she was with me.

I’m looking forward to the next time I can ask my mom for help with my SQL.