To select text value from a group in sql

Question

I want to select the row if that particular string is present in that group

I have a table which contains id and detail column.

Query to create table:

create table emp_detail (id number,details varchar2(20));
insert into emp_detail values(12,'Major')
insert into emp_detail values(12,'Critical')
insert into emp_detail values(12,'high')
insert into emp_detail values(12,'Low')
insert into emp_detail values(13,'Major')
insert into emp_detail values(13,'Low')
insert into emp_detail values(13,'high')
insert into emp_detail values(14,'Critical')
insert into emp_detail values(14,'Low')
insert into emp_detail values(14,'high')

Query result:

Id     Detail
12  Major
12  Critical
12  high
12  Low

13  Major
13  Low
13  high

14  Critical
14  Low
14  high

Output expected

Id     Detail
12  Critical
13  Major
14  Critical

So if there is Critical and Major both present in Detail for a id, then I want to select Only critical.If no critical then major be selected.If only critical then critical will be selected.

score 1 · Answer 1 · answered Sep 11 '19 at 11:47

This is a prioritization query. You can use row_number():

select id, details
from (select ed.*,
             row_number() over (partition by id
                                order by (case details when 'Critical' then 1 when 'Major' then 2 when 'High' then 3 when 'Low' then 4 else 5
                               ) as seqnum
      from emp_detail ed
     ) ed
where seqnum = 1;

You can also phrase this using join:

select id, details
from (select ed.*,
             row_number() over (partition by id
                                order by d.ord nulls last
                               ) as seqnum
      from emp_detail ed left join
           (select 'Critical' as details, 1 as ord from dual union all
            select 'Major' as details, 2 as ord from dual union all
            select 'High' as details, 3 as ord from dual union all
            select 'Low' as details, 4 as ord from dual union all
           ) d
           on ed.details = d.details

     ) ed
where seqnum = 1;

And finally, a fun way to address this uses aggregation and coalesce():

select id,
       coalesce( max(case when details = 'Critical' then details end),
                 max(case when details = 'Major' then details end),
                 max(case when details = 'High' then details end),
                 max(case when details = 'Low' then details end)
               ) as details
from emp_details ed
group by id;

Note: For some reason 'high' does not follow your capitalization conventions, so either fix the data or use 'high' for the above queries.

score 0 · Answer 2 · answered Sep 11 '19 at 11:47

0

use row_number() and length()

select id,detail from 
(select id,detail,row_number() over(partition by id order by length(Detail) desc) rn
from table_name
) a where a.rm=1

answered Sep 11 '19 at 11:47

Zaynul Abadin Tuhin

31,407
5
33
63

score 0 · Answer 3 · answered Sep 11 '19 at 11:51

Try this-

SELECT ID,
CASE 
    WHEN Detail = 4 THEN 'Critical'
    WHEN Detail = 3 THEN 'Major'
    WHEN Detail = 2 THEN 'High'
    WHEN Detail = 1 THEN 'Low'
END Detail
FROM
(
    SELECT ID,
    MAX(
    CASE 
        WHEN Detail = 'Critical' THEN 4
        WHEN Detail = 'Major' THEN 3
        WHEN Detail = 'High' THEN 2
        WHEN Detail = 'Low' THEN 1
    END Detail 
    )
    FROM your_table
    GROUP BY ID
)A

score 0 · Answer 4 · answered Sep 11 '19 at 11:55

You can use MAX( details ) KEEP ( DENSE RANK FIRST ORDER BY ... ) and set the ordering preference using a CASE statement:

Oracle Setup:

create table emp_detail (id, details ) AS
  SELECT 12, 'Major'    FROM DUAL UNION ALL
  SELECT 12, 'Critical' FROM DUAL UNION ALL
  SELECT 12, 'High'     FROM DUAL UNION ALL
  SELECT 12, 'Low'      FROM DUAL UNION ALL
  SELECT 13, 'Major'    FROM DUAL UNION ALL
  SELECT 13, 'High'     FROM DUAL UNION ALL
  SELECT 13, 'Low'      FROM DUAL UNION ALL
  SELECT 14, 'Critical' FROM DUAL UNION ALL
  SELECT 14, 'High'     FROM DUAL UNION ALL
  SELECT 14, 'Low'      FROM DUAL;

Query:

SELECT id,
       MAX( details )
         KEEP (
           DENSE_RANK FIRST
           ORDER BY CASE details
                    WHEN 'Critical' THEN 1
                    WHEN 'Major'    THEN 2
                    WHEN 'High'     THEN 3
                    WHEN 'Low'      THEN 4
                                    ELSE 5
                    END
         ) AS details
FROM   emp_detail
GROUP BY id

Output:

ID | DETAILS 
-: | :-------
12 | Critical
13 | Major   
14 | Critical

db<>fiddle here

To select text value from a group in sql

4 Answers4