[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Distinct query that is not so distinct

Posted on 2011-09-14
21
Medium Priority
?
354 Views
Last Modified: 2012-05-12
I have a VB.NET application and I need a query that will group tags that have identical sets of 3 codes. But I only want to group a maximum of 2 tags (one from each location). Once grouped I would like to compare the data associated with each tag.

In the pic I've drawn a red box around the tags that need to be grouped based on them having an identical composite code (code & code2 & code3). I drew dotted lines to match each tag with another tag (if available) from the opposite location within the group.
 

test-data2.xls
test-data.gif
0
Comment
Question by:KingMooBot
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 9
21 Comments
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36537529
Can this also be like:-

I have swap red green to red blue

COMPOSITECODE	LOC1	LOC2	DATA1	DATA2
XX1YY1ZZ1	CAN	USA	red	blue
XX1YY1ZZ1	CAN	USA	green	blue
XX2YY2ZZ2	CAN	USA	red	red
XX2YY2ZZ2	CAN		red

Open in new window

0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36537532
Reason why I am asking is what is the order of rows from T_Data?
0
 

Author Comment

by:KingMooBot
ID: 36537583
the order doesn't matter
0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:KingMooBot
ID: 36537590
the table is currently ordered by code, then code2, then code3
0
 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 2000 total points
ID: 36537780
select compositecode, max(loc1) loc1, max(loc2) loc2, max(can), max(usa)
from
(
  select code|| code2|| code3 as compositecode, decode(location,'CAN', 'CAN',NULL) LOC1,
  DECODE(LOCATION, 'USA', 'USA', NULL) LOC2,
  decode(row_number() over(partition by code, code2, code3 order by location),1,data,2, data,null) can,
  decode(row_number() over(partition by code, code2, code3 order by location),3,data,4, data,null) usa,
  row_number() over(partition by code, code2, code3, location order by location) rank
  from temp_dev_1
)
group by compositecode, rank
order by 1, 2
0
 

Author Comment

by:KingMooBot
ID: 36537903
that works great. is it easy for me to insert more data columns into this type of query?

this example has "data" but the real table has "data1", data2", "data3", etc.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36537932
Yes its easy to expand this query.
Let us know if you are having any difficulty.
0
 

Author Comment

by:KingMooBot
ID: 36538011
I'm not really seeing it. how do I add data2?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36538267
I will appreciate if you provide me full requirement.
However you can replace data with data2 in below lines
  decode(row_number() over(partition by code, code2, code3 order by location),1,data2,2, data,null) can,
  decode(row_number() over(partition by code, code2, code3 order by location),3,data2,4, data,null) usa,
0
 

Author Comment

by:KingMooBot
ID: 36538297
sorry about that. But I would like to list list both data and data2
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36538349
Whew...Try this:


select X1.compositecode, X1.location as loc1, X2.location as loc2, x1.data as data1, x2.data as data2 
from
(
Select t1.code||t1.code2||t1.code3 as compositecode, t1.location, t1.data
,row_number() over(partition by t1.code+t1.code2+t1.code3 order by t1.data) as rowno 
from @tab T1 inner join
(
Select L1.*, row_number() over(partition by code, code2, code3 order by Location) as LocNo
from
(
Select Code, Code2, Code3, Location from @tab group by Code, Code2, Code3, location
) as L1
) as L2
on L2.code  = T1.Code
and l2.code2 = t1.code2
and l2.code3 = t1.code3
and L2.Location = T1.Location
and L2.LocNo = 1
) as X1
left join 
(
Select t1.code||t1.code2||t1.code3 as compositecode, t1.location, t1.data 
,row_number() over(partition by t1.code+t1.code2+t1.code3 order by t1.data) as rowno 
from @tab T1 inner join
(
Select L1.*, row_number() over(partition by code, code2, code3 order by Location) as LocNo
from
(
Select Code, Code2, Code3, Location from @tab group by Code, Code2, Code3, location
) as L1
) as L2
on L2.code  = T1.Code
and l2.code2 = t1.code2
and l2.code3 = t1.code3
and L2.Location = T1.Location
and L2.LocNo = 2 
) as X2
on X1.compositecode = X2.compositecode
and X1.rowno = x2.rowno
where x1.rowno <=2

Open in new window

0
 

Author Closing Comment

by:KingMooBot
ID: 36538362
works great thanks
0
 

Author Comment

by:KingMooBot
ID: 36538371
sorry dqmq. didn't see your solution until I accepted. Thanks I'll try yours too!
0
 

Author Comment

by:KingMooBot
ID: 36538783
for some reason many of the many of the records returned have data in the wrong column.

ex "green" in "CAN" column
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36538836
Please provide data in excel sheet and desire result.
0
 

Author Comment

by:KingMooBot
ID: 36539117
test data:
 test-data3.xls

expected result:
 test-data3-results.xls
0
 

Author Comment

by:KingMooBot
ID: 36539124
sorry typo in results:
 test-data3-results.xls
0
 

Author Comment

by:KingMooBot
ID: 36539601
not so sure what this was for:
  decode(row_number() over(partition by code, code2, code3 order by location),1,data,2, data,null) can,
  decode(row_number() over(partition by code, code2, code3 order by location),3,data,4, data,null) usa,

I changed it to:
  DECODE(LOCATION, 'CAN', DATA, Null) CAN_DATA,
  DECODE(LOCATION, 'USA', DATA, Null) USA_DATA,
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36539712
My bad, I made small mistake.
I will send the query in a while.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36539717
Please use the following.

select compositecode, max(loc1) loc1, max(loc2) loc2, max(can_data), max(usa_data), max(can_data2) can_data2, max(USA_data2) USA_data2,
rank
from
(
  select code|| code2|| code3 as compositecode, decode(location,'CAN', 'CAN',NULL) LOC1,
  DECODE(LOCATION, 'USA', 'USA', NULL) LOC2,
  decode(location, 'CAN', data) can_data,
  decode(location, 'USA', data) USA_data,
  decode(location, 'CAN', data2) can_data2,
  decode(location, 'USA', data2) USA_data2,
  row_number() over(partition by code, code2, code3, location order by location) rank
  from temp_dev_1
)
group by compositecode, rank
order by rank,1, 2

I was looking in excel sheet, and there were small mistake.
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36539732
>> not so sure what this was for:

Actually I restrict total number of rows to 2.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question