CODEWITHSUNDEEP
sql-server
usefulBee
usefulBee

Reputation: 9702

How to stop condtions if the first one is met?

I have the following OR conditions in a procedure:

    WHERE table1.url LIKE '%_50%' OR table1.url LIKE '%_60%' OR table1.url LIKE '%_70%'

This where clause brings all urls that contain these values (_50, _60, _70) for each set of data that is uniquely identified by a ProductID. Instead, I would like to get only a single record for each Product that meets a single condition of those values. What is the best approach to this problem?

Upvotes: 1

Views: 42

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72185

You can use an ORDER BY clause, like:

ORDER BY CASE 
            WHEN table1.url LIKE '%_50%' THEN 1
            WHEN table1.url LIKE '%_60%' THEN 2
            WHEN table1.url LIKE '%_70%' THEN 3
         END

Using TOP 1 in the SELECT clause will get you the record required.

Edit:

To get one record for each partition you can use the following query:

SELECT *
FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY CASE 
                                           WHEN table1.url LIKE '%_50%' THEN 1
                                           WHEN table1.url LIKE '%_60%' THEN 2
                                           WHEN table1.url LIKE '%_70%' THEN 3
                                         END
                            ORDER BY table1.url) AS rn
FROM table1
WHERE table1.url LIKE '%_50%' OR 
      table1.url LIKE '%_60%' OR 
      table1.url LIKE '%_70%' ) AS t
WHERE t.rn = 1

Upvotes: 4

Related Questions