• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

SQL Distinct query that is not so distinct

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
KingMooBot
Asked:
KingMooBot
  • 11
  • 9
1 Solution
 
Devinder Singh VirdiCommented:
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
 
Devinder Singh VirdiCommented:
Reason why I am asking is what is the order of rows from T_Data?
0
 
KingMooBotAuthor Commented:
the order doesn't matter
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
KingMooBotAuthor Commented:
the table is currently ordered by code, then code2, then code3
0
 
Devinder Singh VirdiCommented:
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
 
KingMooBotAuthor Commented:
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
 
Devinder Singh VirdiCommented:
Yes its easy to expand this query.
Let us know if you are having any difficulty.
0
 
KingMooBotAuthor Commented:
I'm not really seeing it. how do I add data2?
0
 
Devinder Singh VirdiCommented:
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
 
KingMooBotAuthor Commented:
sorry about that. But I would like to list list both data and data2
0
 
dqmqCommented:
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
 
KingMooBotAuthor Commented:
works great thanks
0
 
KingMooBotAuthor Commented:
sorry dqmq. didn't see your solution until I accepted. Thanks I'll try yours too!
0
 
KingMooBotAuthor Commented:
for some reason many of the many of the records returned have data in the wrong column.

ex "green" in "CAN" column
0
 
Devinder Singh VirdiCommented:
Please provide data in excel sheet and desire result.
0
 
KingMooBotAuthor Commented:
test data:
 test-data3.xls

expected result:
 test-data3-results.xls
0
 
KingMooBotAuthor Commented:
sorry typo in results:
 test-data3-results.xls
0
 
KingMooBotAuthor Commented:
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
 
Devinder Singh VirdiCommented:
My bad, I made small mistake.
I will send the query in a while.
0
 
Devinder Singh VirdiCommented:
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
 
Devinder Singh VirdiCommented:
>> not so sure what this was for:

Actually I restrict total number of rows to 2.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now