Need SQL query to get conditional output

Question

There is a table Table1 with rows shown below. Column Label stores priority for column Tag.

Also in column Label - L1 is first priority, L2 is second and L3 is least priority.

I have Value column which holds values for tag and which can be null.

RecordNo.   Lable   Tag  Value
----------------------------------------------
1           L1      T1  
2           L2      T1   D12
3           L3      T1   D13
4           L1      T2   D21
5           L2      T2  
6           L3      T3  
7           L2      T3   D31
8           L2      T4  
9           L3      T4   D41
10          L3      T5   D51

I want to write a query to get the output as below.

For every Tag, if value not found for L1 then we will search for L2 and if for L2 data not found then search for L3.So at any point it should return not null value for tag.

Output will look like below.

RecordNo.   Lable   Tag  Value
---------------------------------------------------------
2           L2      T1   D12
4           L1      T2   D21
7           L2      T3   D31
9           L3      T4   D41
10          L3      T5   D51

Can anyone please check on above query?

Thanks in advance.

hi and welcome to stack overflow. generally here we expect you to have a go at it yourself, and then show us any code you have (even if it's not working). we can help you to debug your code, but won't write it for you. — Taryn East
– Taryn East, Commented Jun 18, 2015 at 4:43
"Can anyone please check on above query" - looks like you missed out on posting it. Please post the query you have as of now — Raj
– Raj, Commented Jun 18, 2015 at 4:44

Felix Pamittan · Accepted Answer · 2015-06-18 04:49:03Z

3

You can use ROW_NUMBER:

;WITH Cte AS(
    SELECT *,
        RN = ROW_NUMBER() OVER(PARTITION BY Tag ORDER BY Lable)
    FROM Table1
    WHERE Value IS NOT NULL
)
SELECT 
    RecordNo, Lable, Tag, Value
FROM Cte
WHERE RN = 1

SQL Fiddle

answered Jun 18, 2015 at 4:49

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user4916191 Over a year ago

thanks for your comment, I just want to know is it possible to handle a situation if priority for Label is changing i.e. for some condition priority is L1, L2, L3 where L1 is highest and L3 is least priority. For some other condition L2, L3, L1 is priority where L2 is highest and L1 is least priority.

Felix Pamittan Over a year ago

How do you determine the priority? Is it stored in a table?

user4916191 Over a year ago

Label priorities are not stored in table, they are controlled by external source.

Felix Pamittan Over a year ago

What do you mean? I suggest you ask a new question.

user4916191 Over a year ago

What i mean to say is order of Label column can be user specific. Means for above given it will always try to get output with Label data as Ascending order i.e. L1,L2,L3. But is it possible to get result with L2,L3,L1 order.

JamieD77 · Accepted Answer · 2015-06-18 04:54:48Z

0

cte with Min should do the trick

;WITH cte AS (
    SELECT  Tag,
            MIN(Lable) AS Lable
    FROM    Table1
    WHERE   Value IS NOT NULL 
    GROUP BY Tag
)
SELECT  *
FROM    Table1 t1
        JOIN cte c ON t1.Lable = c.Lable 
            AND t1.Tag = c.Tag

answered Jun 18, 2015 at 4:54

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Comments

Hell Boy · Accepted Answer · 2015-06-18 05:02:30Z

Use This Code:

create table #test 
(
Record_no int,
label varchar(4),
Tag varchar(4),
Value varchar(6)
)
insert into #test values(1,'L1','T1','')
insert into #test values(2,'L2','T1','D12')
insert into #test values(3,'L3','T1','D13')
insert into #test values(4,'L1','T2','D21')
insert into #test values(5,'L2','T2','')
insert into #test values(6,'L3','T3','')
insert into #test values(7,'L2','T3','D31')
insert into #test values(8,'L2','T4','')
insert into #test values(9,'L3','T4','D41')
insert into #test values(10,'L3','T5','D51')

Select * from #test

;With Cte as
(
Select *,ROW_NUMBER() over (partition by tag order by tag) as rownum
from #test
where Value<>''
)
select Record_no,label,tag,value from CTE 
where ROWNUM=1

Output:

Record_no   label   tag value
   2         L2     T1  D12
   4         L1     T2  D21
   7         L2     T3  D31
   9         L3     T4  D41
  10         L3     T5  D51

BICube · Accepted Answer · 2015-06-18 06:36:37Z

if we assume table name is Records then the query will be as follows.

with recordcte(recordno, label, tag, value,Rank) as
(select recordno, 
        label,
        tag,
        value, 
        rank() over(partition by Tag order by newid() ) 
from records where value is not null)
select recordno,
       label,
       tag,
       value 
from recordcte where Rank=1

the following query, which is definition of CTE(Common Table Expression)

select recordno, label, tag, value, rank() over(partition by Tag order by newid() from records where value is not null)

is eliminating the rows having null in value column and generating a rank for the rows by partitioning them on tag value.

the following query on CTE

select recordno, label,tag,value from recordcte where Rank=1

will display only the rows having the rank 1 so that you will get the tag associated with value that also the first one associated with the value.

I hope this will fulfill your requirement.

anirvachniya · Accepted Answer · 2015-06-18 08:20:00Z

0

You can set the order for L1, L2, L3 or L3,L1,L2 or L2,L3,L1

select distinct Lable, Tag, Value from Table1 where Lable = L1 union select distinct Lable, Tag, Value from Table1 where
Lable = L2 and Tag not in(select distinct Tag from Table1 where Lable in (L1)) union select distinct Lable, Tag, Value from Table1 where
Lable = L3 and Tag not in(select distinct Tag from Table1 where
Lable in (L1,L2))****

answered Jun 18, 2015 at 8:20

anirvachniya

112 bronze badges

Collectives™ on Stack Overflow

Need SQL query to get conditional output

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related