CODEWITHSUNDEEP
sqlpostgresql
Edwin
Edwin

Reputation: 562

SQL: Select random row with condition priority

I have the condition where I'd like to select a random user based on whether they match one of three conditions:

  1. If they match the ZIP code
  2. If they match the city name
  3. If they match the state name

If one is matched, then return the randomly selected result.

Essentially something like this (~pseudo-code):

SELECT * FROM user
INNER JOIN address ON address.id = user.address_id
WHERE address.zipcode = '00000' IF EXISTS ELSE (
    WHERE address.province = 'State Name' IF EXISTS ELSE
        (WHERE address.city = 'City Name')
)
ORDER BY RANDOM() LIMIT 1;

Is there any nice way to do this or is it easier just to split it into three separate queries?

Upvotes: 2

Views: 1445

Answers (2)

Lucas Fonseca
Lucas Fonseca

Reputation: 376

I have some doubts about your question, but If I understood, this will solve:

Use variables to your values and check if it's not empty

DECLARE @zipcode VARCHAR(8) = '12425190'
DECLARE @province VARCHAR(50) = 'São Paulo'
DECLARE @city VARCHAR(50) = 'Pindamonhangaba'

SELECT * FROM user
INNER JOIN address ON address.id = user.address_id
WHERE (@zipcode = null OR address.zipcode = @zipcode) &&
      (@province = null OR address.province = province) &&
      (@city = null OR address.city = @city)    
ORDER BY RANDOM() LIMIT 1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

If I understand correctly, you want a prioritization, starting with zip code. If so:

SELECT u.*
FROM user u INNER JOIN
     address a
     ON a.id = u.address_id
WHERE a.zipcode = '00000' OR
      a.province = 'State Name' OR
      a.city = 'City Name'
ORDER BY (a.zipcode = '00000')::INT DESC,
         (a.province = 'State Name')::INT DESC,
         (a.city = 'City Name')::INT DESC,
         random();
LIMIT 1;

If you just want any of the three to match -- and a random selection among those -- then use ORDER BY random().

Upvotes: 2

Related Questions