0

My query is like this

select * from mytable where rank <= 10000 and priority = 1

I want to rewrite it like this.

  • Select records which are under a specific value and priority is one.
  • If no records found with priority = 1, then run the query without priority.
select * from mytable where rank <= 10000

I know it can be done with php but can it can done in one query instead using php ?

thanks

2
  • using a single query or within a stored procedure?
    – McNets
    Commented May 2, 2017 at 20:24
  • single query please.
    – formul1
    Commented May 2, 2017 at 20:27

1 Answer 1

0

There may be a more elegant or performant solution, but here's a simple option:

select * from mytable
 where rank <= 10000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE rank <= 10000
                          AND priority = 1
                      )
       )
;

This works as follows: the NOT EXISTS statement will either evaluate to TRUE for every record where the rank meets our criteria, or to FALSE for every such record. So, if none of the records that meet our other criteria have priority = 1, this is TRUE for all those records, and all records that meet our other criteria will be returned.

If there are records that meet our other criteria and have priority = 1, then this statement will be FALSE for all records that meet our other criteria. In that case, only records where the other branch of the OR is TRUE - where priority = 1 - will be returned.

NOTE: for this to work properly, the NOT EXISTS subquery must check all the query's criteria. If you actually want records where:

  • fieldA is between Jan 1 2017 and March 31 2017; and
  • fieldB is less than 240000

Then the query would have to be:

select * from mytable
 where fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
   and fieldB < 240000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
                          AND fieldB < 240000
                          AND priority = 1
                      )
       )
;
2
  • will the OR do the job ? SELECT ASIN FROM asins WHERE ReviewsNumUpdated<'2017-04-02 20:57:02' AND SalesRank<100000 OR Priority=1 LIMIT 0,10
    – formul1
    Commented May 2, 2017 at 20:58
  • @formul1 no, you must use not exists
    – McNets
    Commented May 2, 2017 at 21:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.