CODEWITHSUNDEEP
mysqlsql
freepublicview
freepublicview

Reputation: 736

Writing better SQL code by avoiding redundancy

i have a sql query like this :

SELECT Code 
FROM xyz 
WHERE xyz.Code  IN  ('20','10') AND price = 
    (select min(price) FROM xyz  WHERE CODE IN  ('20','10'));

Desired Output After Query : 10

Table xyz :-

Price : 1 2 1

Code : 10 20 30

Is there any better way to write this sql statement? Because in my sql statement "WHERE CODE IN ('20','10')" gets repeated twice, and i am trying to avoid calling this statement twice

Upvotes: 1

Views: 297

Answers (2)

Andomar
Andomar

Reputation: 238048

You could use a join to filter, instead of a where in condition. That would allow you to have the joined table re-use the condition from the first table.

select  Code 
from    (
        select  *
        from    xyz
        where   code in ('20','10')
        ) t1 
inner join
        (
        select  code
        ,       min(price) as min_price
        from    xyz
        group by
                code
        ) filter
on      filter.code = t1.code
        and filter.min_price = t1.price

Upvotes: 0

Mark Byers
Mark Byers

Reputation: 837936

If you want to receive only one row you can use ORDER BY and LIMIT:

SELECT Code
FROM xyz
WHERE xyz.Code IN ('20','10')
ORDER BY price
LIMIT 1

Upvotes: 6

Related Questions