CODEWITHSUNDEEP
sqloracle-database
Stephan
Stephan

Reputation: 43013

How to match only the first row matching a condition?

Here is my data:

  INT       CHAR(2)         CHAR(4)
+------+--------------+------------+
|  ID  |  SHORT_CODE  | LONG_CODE  |
+------+--------------+------------+
|   1         01           0100
|   2         01           0110
|   3         01           0120
|   4         02           0200
|   5         02           0212
|   6         02           0299
|   7         02           0211

I'm looking for a query that will output this result:

+--------------+------------+-------------+
|  SHORT_CODE  | LONG_CODE  |   IS_FIRST  |
+--------------+------------+-------------+
|     02           0200          false
|     02           0211          true
|     02           0212          false
|     02           0299          false

Here is what I tried

SELECT 
    short_code,
    long_code,
    CASE
       WHEN long_code LIKE '021%'
       THEN 'true'
       ELSE 'false'
    END as is_first
FROM
    MY_TABLE
WHERE
    short_code='02'
ORDER BY
    long_code ASC;

This query will print also true for 0212. I want true for only the first matching row.

How can I achieve that ?

The sample code in this question is available here.

Oracle 10gR2

Upvotes: 4

Views: 1968

Answers (3)

Kevin Cook
Kevin Cook

Reputation: 1932

SELECT m.*,
(CASE WHEN b.Id IS NOT NULL THEN 'True' ELSE 'False' END) as First
FROM MY_TABLE m
LEFT JOIN 
(
    SELECT Id FROM
    (
    SELECT Id 
    FROM MY_TABLE
    WHERE Long_Code LIKE '021%'
    AND Short_Code = '02'
    ORDER BY Long_Code
    ) C
    WHERE RowNum = 1
 ) B
 ON b.Id = m.Id
 WHERE m.Short_Code = '02';

Upvotes: 0

Mureinik
Mureinik

Reputation: 310993

You could jimmy-rig row_number() to do this, but it may be a bit cumbersome. The best idea I could find was to create a pseudo-column prefix so I could sort all the matching rows first, and then sort by long_code:

SELECT  short_code, 
        long_code, 
        CASE row_number() over (order by prefix desc, long_code asc) 
           WHEN 1 THEN 'true' 
           ELSE 'false' 
        END AS is_first
FROM    (SELECT short_code,
                long_code,
                CASE
                   WHEN long_code LIKE '021%' THEN 1
                   ELSE 0
                END as prefix
          FROM  my_table
          WHERE short_code='02'
         )
ORDER BY long_code ASC;

Results also shown in this SQLFiddle.

Upvotes: 1

Andomar
Andomar

Reputation: 238048

You can create an extra column is_match that shows if the row matches the like '021%' condition. Then you can assign an ascending row number rn within both the partition of rows that match and the partition of rows that do not match. The row that matches and has row number one is the row you're looking for.

SELECT  short_code
,       long_code
,       CASE
        WHEN is_match = 1 and rn = 1 THEN 'true'
        ELSE 'false'
        END as is_first
FROM    (
        SELECT  short_code
        ,       long_code
        ,       is_match
        ,       row_number() over (
                    partition by is_match
                    order by long_code) as rn
        FROM    (
                SELECT  short_code
                ,       long_code
                ,       case 
                        when long_code like '%021%' then 1
                        else 0
                        end as is_match
                FROM    MY_TABLE
                WHERE   short_code = '02'
                ) s1
        ) s2
ORDER BY
        long_code;

See it working at SQL Fiddle.

Upvotes: 1

Related Questions