How to give unique number to every different group of numbers ?
-
Ok... now that I've read the post that I missed, I've added 'P6' to the list of PartNumbers with all '2's for 3 entries to test.
--Add 'P6' to meet the requirements stated in
--https://www.sqlservercentral.com/forums/topic/how-to-give-unique-number-to-every-different-group-of-numbers#post-4067708
create table #parts
(
PartNumber varchar(50),
PartValue int,
UniqueNumber int
)
insert into #parts(PartNumber,PartValue,UniqueNumber)
values
('P1',1,NULL),
('P1',2,NULL),
('P1',3,NULL),
('P1',4,NULL),
('P2',1,NULL),
('P2',2,NULL),
('P3',1,NULL),
('P3',2,NULL),
('P3',3,NULL),
('P4',1,NULL),
('P4',2,NULL),
('P4',3,NULL),
('P5',1,NULL),
('P5',2,NULL),
('P6',2,NULL), --Added to test given requirements
('P6',2,NULL), --Added to test given requirements
('P6',2,NULL) --Added to test given requirements
;I tested Mark's shot at it. It not only does Relational Division but it also does a little Relational Multiplication to form some duplicated rows in the presence of 'P6'.
I tested kah's entry. It works for most everything but it fails to include P6 in the same unique number as the parts that have 1,2,3 as an entry even though it, as per the requirements in the post I previously missed, has the same number of entries and the same sum of the entries for PartValue.
Then I tested Phil's. Almost spot on... just needed a little tweak here and there. Here are the tweaks I made and it correctly produces the answers requested according to the requirements in the post I previously missed.
WITH counts
AS (SELECT p.PartNumber
,ct = COUNT(1) --Changed
,sm = SUM(p.PartValue) --Changed
FROM #parts p
GROUP BY p.PartNumber)
,grouped
AS (SELECT counts.PartNumber
,rnk = DENSE_RANK() OVER (ORDER BY counts.ct,counts.sm) --Changed
FROM counts)
SELECT p.PartNumber
,p.PartValue
,UniqueNumber = g.rnk
FROM #parts p
JOIN grouped g
ON g.PartNumber = p.PartNumber
ORDER BY g.PartNumber
,p.PartValue;Phil's code (along with the mods I added to handle the "P6" requirements) has the added advantage of the "unique number" also showing the relative order by sum of the PartValue.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Here's the rendition I came up with. It produces the same answer as Phil's. The % of Batch in the Actual Execution Plan says that mine is 27% faster but I don't know if I'd believe that or not. I think its because mind does only one scan of the table and then two in memory table spools where Phil's does two table scans and I've seen the % of Batch be 100% backwards before (Grant Fritchey has one of my examples in one of his books). "One good test is worth a thousand opinions" and I don't have the time tonight to setup a "good test".
WITH
cteCounts AS
(
SELECT PartNumber
,PartValue
,C = COUNT(PartValue) OVER (PARTITION BY PartNumber)
,S = SUM (PartValue) OVER (PARTITION BY PartNumber)
FROM #parts
)
SELECT PartNumber
,PartValue
,UniqueNumber = DENSE_RANK() OVER (ORDER BY C,S)
FROM cteCounts
ORDER By PartNumber
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I believe that this can be simplified as follows
CREATE TABLE #parts ( PartNumber varchar(50)
, PartValue int
, UniqueNumber int );
INSERT INTO #parts ( PartNumber, PartValue, UniqueNumber )
VALUES ( 'P1', 1, NULL )
, ( 'P1', 2, NULL )
, ( 'P1', 3, NULL )
, ( 'P1', 4, NULL )
, ( 'P2', 1, NULL )
, ( 'P2', 2, NULL )
, ( 'P3', 1, NULL )
, ( 'P3', 2, NULL )
, ( 'P3', 3, NULL )
, ( 'P4', 1, NULL )
, ( 'P4', 2, NULL )
, ( 'P4', 3, NULL )
, ( 'P5', 1, NULL )
, ( 'P5', 2, NULL )
, ( 'P6' ,2, NULL ) --Added to test given requirements
, ( 'P6' ,2, NULL ) --Added to test given requirements
, ( 'P6' ,2, NULL ); --Added to test given requirements
SELECT p.PartNumber
, UniqueNumber = DENSE_RANK() OVER (ORDER BY COUNT(p.PartValue), SUM(p.PartValue))
FROM #parts AS p
GROUP BY p.PartNumber
DROP TABLE #parts;PartNumber UniqueNumber
---------- ------------
P2 1
P5 1
P6 2
P3 2
P4 2
P1 3 -
I agree... that's a great simplification. But it doesn't return all the rows with the unique number.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply