CODEWITHSUNDEEP
sqloracle-database
user6852928
user6852928

Reputation: 11

SQL Group by query - choose row in priority

I work in Oracle database, where I need create query for following table (result of query).

CUSTOMER_REFENRECE LQ   UN   SPECIAL INSTRUCTIONS
------------------ ---- ---- --------------------------------
FI020098           null null Not Restricted as per ADR SP 375
FI020098           HZ   1219 HZ UN1219
FI020098           LQ   1219 UN1219
FI020097           null null null
FI020097           LQ   1219 UN1219

I need get just one row per CUSTOMER_REFERENCE in order to:

HZ > LQ > NULL

If there is one HZ, then just return HZ row (skip null)

FI020098 - HZ- 1219- HZ UN1219

If there isnt any HZ and at least one LQ, return row with LQ

FI020097 - LQ - 1219 - UN1219

Table

Upvotes: 0

Views: 389

Answers (1)

MT0
MT0

Reputation: 167822

Use the ROW_NUMBER() analytic function and map the LQ values to numbers in order of sort importance using DECODE() (or CASE):

SELECT *
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_reference
           ORDER BY DECODE( LQ, 'HZ', 1, 'LQ', 2, 3 ) ASC
         ) AS rn
  FROM   table_name t
)
WHERE  rn = 1;

Upvotes: 2

Related Questions