CODEWITHSUNDEEP
sqloracle-database
mitkosoft
mitkosoft

Reputation: 5316

Fetch SQL rows with priority

I have following tables structure:

forms

RID | MODULE
------------
1   | indiv
2   | indiv
3   | indiv

translations

RID | LANG | VALUE | MODULE | TAG |
-----------------------------------
1   |   en |car    |        |     |
1   |   en |truck  |indiv   |     |
1   |   en |boat   |indiv   |C100 |
2   |   en |hat    |        |     |
3   |   en |cat    |        |     |
3   |   en |dog    |indiv   |     |
4   |   en |light  |        |     |
5   |   en |dark   |        |     |

I need to fetch only one row per RID from translations table, based on additional (but not mandatory) parameters for module and tag columns, i.e.:

RESULT without input parameters:

RID | LANG | VALUE | MODULE | TAG |
-----------------------------------
1   |   en |car    |        |     |
2   |   en |hat    |        |     |
3   |   en |cat    |        |     |

RESULT with one input parameter module='indiv':

RID | LANG | VALUE | MODULE | TAG |
-----------------------------------
1   |   en |truck  |indiv   |     |
2   |   en |hat    |        |     |
3   |   en |dog    |indiv   |     |

If I have two input parameters the result to be:

RESULT with two parameters: module='indiv' AND tag='c100'

RID | LANG | VALUE | MODULE | TAG |
-----------------------------------
1   |   en |boat   |indiv   |C100 |
2   |   en |hat    |        |     |
3   |   en |dog    |indiv   |     |

How can I achieve this with SQL only on ORACLE DB server? A query example for the last case with two parameters will be enough for me as previous cases are subsets from last one with NULL of these columns I believe. If you think that all these cases are too different and require different SQL statements, you are more than welcome to write them as well.

Thank you!

Upvotes: 0

Views: 98

Answers (2)

MT0
MT0

Reputation: 167982

SELECT *
FROM   (
  SELECT  t.*,
          ROW_NUMBER() OVER (
            PARTITION BY RID
            ORDER BY CASE
                     WHEN module = LOWER( :mod ) AND tag = UPPER( :tag ) THEN 1
                     WHEN                            tag = UPPER( :tag ) THEN 2
                     WHEN module = LOWER( :mod ) AND tag IS NULL         THEN 3
                     WHEN module IS NULL         AND tag IS NULL         THEN 4
                                                                         ELSE 5
                     END
        ) AS rn
  FROM  translations t
  WHERE ( module IS NULL OR module = LOWER( :mod ) )
  OR    ( tag IS NULL OR tag = UPPER( :tag ) )

)
WHERE  rn = 1;

Upvotes: 2

Paul
Paul

Reputation: 1167

I think this should do.

SELECT *
FROM (
  SELECT F.RID,
         T.LANG,
         T.VALUE,
         T.MODULE,
         T.TAG,
         RANK() OVER(PARTITION BY F.RID
                     ORDER BY DECODE(T.MODULE, :module, 1, 2),
                              DECODE(T.TAG,    :tag,    1, 2))   RANK
  FROM   forms F INNER JOIN translations T
    ON T.RID = F.RID)
WHERE RANK = 1

So you rank rows with MODULE = :module or/and TAG = :tag higher. You still need something to do with ties, but you get the idea. RANK leaves ties, ROW_NUMBER does not.

I put MODULE higher than TAG because of your examples. You might need to change it if you can input tags without modules.

And also, DECODE maps NULL to NULL, so if :module is not set, you will get match with rows having NULL in MODULE.

Upvotes: 0

Related Questions