May 31, 2016 at 8:52 pm
Hi
I am having a situation where I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_code
Dataset
idalert_codeproduct_code
1A 123
4A 456
7A 789
2B 123
5B 456
8B 789
3C 123
6C 456
9C 789
Result Should be for example
1)
A 123
B 456
C 789
OR
2)
A 456
B 789
C 123
OR other combinations that meet the requirement of displaying all alert codes and within each alert code a product code exist within the alert code and that not exist in other alert codes.
There may be many other Alert_codes and many other Product Codes in a Many to Many relation.
Not sure how to handle this query.
Any help will be greatly appreciated
Regards
June 1, 2016 at 7:20 am
Hi and welcome to the forums. This is a great place to start for getting you an answer. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 1, 2016 at 8:10 am
as an example of how to present some sample data see below and here https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
SELECT * FROM #yourtable ORDER BY ID
do you ALWAYS have the same number of rows for each "alert_code"?
what do you want as results if you add these extra rows?
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 1, 2016 at 8:48 am
This MIGHT work but I'm not sure since I'm having a hard time understanding your requirement and I have a feeling your example data set isn't reflective of what it really looks like. That being said...
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
--SELECT * FROM #yourtable ORDER BY ID
SELECT
x.alert_code,
x.product_code
FROM (
SELECT
alert_code,
product_code,
DENSE_RANK() OVER (PARTITION BY product_code ORDER BY alert_code) AS MyRank1,
DENSE_RANK() OVER (PARTITION BY alert_code ORDER BY product_code) AS MyRank2
FROM #yourtable
) x
WHERE x.MyRank1 = x.MyRank2
DROP TABLE #yourtable
Cheers,
June 1, 2016 at 8:52 am
yb751 (6/1/2016)
This MIGHT work but I'm not sure since I'm having a hard time understanding your requirement and I have a feeling your example data set isn't reflective of what it really looks like. That being said...
CREATE TABLE #yourtable(id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
--SELECT * FROM #yourtable ORDER BY ID
SELECT
x.alert_code,
x.product_code
FROM (
SELECT
alert_code,
product_code,
RANK() OVER (PARTITION BY product_code ORDER BY alert_code) AS MyRank1,
RANK() OVER (PARTITION BY alert_code ORDER BY product_code) AS MyRank2
FROM #yourtable
) x
WHERE x.MyRank1 = x.MyRank2
DROP TABLE #yourtable
Cheers,
I would use DENSE_RANK() rather than RANK().
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 1, 2016 at 9:03 am
drew.allen (6/1/2016)
I would use DENSE_RANK() rather than RANK().
Drew
Good Point, no way of knowing of if the data has duplicate alert/product code pairs. I'll edit the code.
June 1, 2016 at 9:09 am
some more testdata to play with
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 1, 2016 at 9:12 am
hmmm...yeah that throws a wrench in things. I just wished we knew more.
June 1, 2016 at 9:18 am
yb751 (6/1/2016)
hmmm...yeah that throws a wrench in things. I just wished we knew more.
hehe....pity the OP didnt post an update when (s)he peeked out from behind the sofa, half an hour ago 🙂
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 1, 2016 at 12:12 pm
Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.
The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);
--SELECT * FROM #yourtable ORDER BY ID
SELECT
z.alert_code,
z.product_code,
ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank
INTO #final
FROM (
SELECT
y.alert_code,
y.product_code,
DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,
ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2
FROM #yourtable y
CROSS APPLY(
SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2
) x
) z
WHERE z.MyRank1 = z.MyRank2
SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code
DROP TABLE #yourtable
DROP TABLE #final
June 1, 2016 at 12:27 pm
yb751 (6/1/2016)
Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);
--SELECT * FROM #yourtable ORDER BY ID
SELECT
z.alert_code,
z.product_code,
ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank
INTO #final
FROM (
SELECT
y.alert_code,
y.product_code,
DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,
ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2
FROM #yourtable y
CROSS APPLY(
SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2
) x
) z
WHERE z.MyRank1 = z.MyRank2
SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code
DROP TABLE #yourtable
DROP TABLE #final
oh, how I love these lateral thinking problems 😛
if only the OP would post back with a sample data set that covers all possibilites then I could sleep easy :w00t:
looking at your latest code I ma not sure that it delivers what is required
I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_code
surely that means that all 8 "alert_codes" are included in the result set
SELECT DISTINCT alert_code FROM #yourtable
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 1, 2016 at 12:34 pm
J Livingston SQL (6/1/2016)
oh, how I love these lateral thinking problems 😛
if only the OP would post back with a sample data set that covers all possibilites then I could sleep easy :w00t:
looking at your latest code I ma not sure that it delivers what is required
I need to pick from a data set like the one below a unique value from alert_code and associate a non duplicated product_codesurely that means that all 8 "alert_codes" are included in the result set
SELECT DISTINCT alert_code FROM #yourtable
LOL...yes, I do agree that it's not perfect but I'm purely working off the data you provided for fun (or torture). In that scenario it would be impossible to provide a unique product code for each available alert code. That being said I'm off the case until we get more info. 😉
June 1, 2016 at 12:40 pm
yb751 (6/1/2016)
Ok so after a few voodoo spells and a couple chickens later I came up with this. If it looks weird, that's because it is. I'm pretty sure this can be reworked but for whatever it's worth.The following code will give you a unique product code for each alert code amongst those that qualify. However, there is no guarantee which valid product will be selected.
CREATE TABLE #yourtable(
id INTEGER NOT NULL
,alert_code VARCHAR(1) NOT NULL
,product_code INTEGER NOT NULL
);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (1,'A',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (4,'A',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (7,'A',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (2,'B',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (5,'B',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (8,'B',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (3,'C',123);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (6,'C',456);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (9,'C',789);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (10,'D',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (11,'E',1000);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (12,'F',1);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (13,'G',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (14,'H',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (15,'A',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (16,'B',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (17,'C',999);
INSERT INTO #yourtable(id,alert_code,product_code) VALUES (18,'D',999);
--SELECT * FROM #yourtable ORDER BY ID
SELECT
z.alert_code,
z.product_code,
ROW_NUMBER() OVER (PARTITION BY z.product_code ORDER BY z.alert_code) AS FinalRank
INTO #final
FROM (
SELECT
y.alert_code,
y.product_code,
DENSE_RANK() OVER (ORDER BY alert_code) AS MyRank1,
ROW_NUMBER() OVER (PARTITION BY y.alert_code ORDER BY y.alert_code) AS MyRank2
FROM #yourtable y
CROSS APPLY(
SELECT TOP (SELECT DISTINCT COUNT(product_code) FROM #yourtable) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS maxValues FROM dbo.SysColumns sc1, dbo.SysColumns sc2
) x
) z
WHERE z.MyRank1 = z.MyRank2
SELECT alert_code, product_code FROM #final WHERE FinalRank = 1 ORDER BY alert_code
DROP TABLE #yourtable
DROP TABLE #final
I don't like it, because G is excluded and 456 is also excluded and both can be included if you change B - 999 to B - 456. Of course, I'm not the OP, and they may love it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 1, 2016 at 1:23 pm
Trust me, I'm not crazy about it either. I have a couple of other ideas but until I hear from the OP I'd just be taking more shots in the dark.
June 1, 2016 at 4:15 pm
Thanks for your prompt response.
I would like to provide more details to the requirements.
In my challenge, 1 product can has thousands of alerts and I need to report all Alert_Codes and select for each alert a product_code that hasn't been reported in previous alert
The idea is that all alert_codes should appear once A,B,C...ZZZZZ. For each alert_code I should display 1 of the infinite product_code numbers associated to the alert_code, the only condition is that they do not repeat in the display. The situation is that many of the product_code are repeated in every alert_code and not able to select not repeated. I am trying not to use CURSORS, which would solve the challenge.
So, the final output should be something like
A->123
B->456
C->789
D->135
E->246
.
.
AA->999
AZ->9999
.
.
ZZZZZ->9999999
etc...
The alert_code is an integer in my real scenario, but I just decided to use letters to make simpler.
The product_code is an integer in my real scenario, I just used 3 digits to make it simpler and easy to differentiate.
I hope my requirements are better explained.
Thanks for your help.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply