How to give unique number to every different group of numbers ?

Question

How to give unique number to every different group of numbers ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118

July 24, 2022 at 10:33 pm

I work on sql server 2019 i face issue i need to give unique number to every group of numbers without using string aggreagte or stuff

original table as below :

 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)

expected result as below

what i try

SELECT
p.PartNumber,
p.PartValue,
p2.Parts,
NewUniqueNumber = DENSE_RANK() OVER (ORDER BY p2.Parts)
FROM #parts p
JOIN (
SELECT
p2.PartNumber,
STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts
FROM #parts p2
GROUP BY
p2.PartNumber
) p2 ON p2.PartNumber = p.PartNumber;

it give me expected result but i don't need to use this logic

are there are another logic without using string aggregate or stuff

i need to use another logic depend on sum numbers or count it

This topic was modified 2 years, 5 months ago by ahmed_elbarbary.2010.

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ahmed_elbarbary.2010 Hall of Fame Points: 3118 More actions · Answer 1

the goal mfrom asking question is to get result above without string aggreagte or comma separated

suppose i have

p1 1,2,3

p2 2,2,2

both p1 and p2 both have same count and same sum

so are there are another solution without

using string aggregate

Phil Parkin SSC Guru Points: 246980 More actions · Answer 2

As long as you don't mind which order the 'UniqueNumber' is assigned in, this might work:

WITH counts
AS (SELECT p.PartNumber
          ,ctsm = COUNT(1) + SUM(p.PartValue)
    FROM #parts p
    GROUP BY p.PartNumber)
    ,grouped
AS (SELECT counts.PartNumber
          ,rnk = DENSE_RANK() OVER (ORDER BY counts.ctsm)
    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;

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 3

ahmed_elbarbary.2010 wrote:

I work on sql server 2019 i face issue i need to give unique number to every group of numbers without using string aggreagte or stuff

original table as below :

 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)

expected result as below

what i try

SELECT
p.PartNumber,
p.PartValue,
p2.Parts,
NewUniqueNumber = DENSE_RANK() OVER (ORDER BY p2.Parts)
FROM #parts p
JOIN (
SELECT
p2.PartNumber,
STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts
FROM #parts p2
GROUP BY
p2.PartNumber
) p2 ON p2.PartNumber = p.PartNumber;

it give me expected result but i don't need to use this logic

are there are another logic without using string aggregate or stuff

i need to use another logic depend on sum numbers or count it

I don't understand what you want. How is UniqueNumber defined (it doesn't seem to be unique)?

What rule makes different rows have the same UniqueNumber ?

How do you identify a "group of numbers"?

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 4

This looks like a 'relational division' problem. Solution here not pretty but should work.

WITH Grps AS (
SELECT PartNumber,COUNT(*) AS GroupCount
FROM #parts
GROUP BY PartNumber
),
OrderedSrc AS (
SELECT PartNumber,PartValue,ROW_NUMBER() OVER(PARTITION BY PartNumber ORDER BY PartValue) AS rn
FROM #parts
),
Matches AS (
SELECT p1.PartNumber AS PartNumberLHS,p2.PartNumber AS PartNumberRHS,COUNT(*) AS MatchCount
FROM OrderedSrc p1
INNER JOIN OrderedSrc p2 ON p2.PartNumber > p1.PartNumber
AND p2.PartValue = p1.PartValue
AND p2.rn = p1.rn
GROUP BY p1.PartNumber, p2.PartNumber
),
UniqueNumbers AS (
SELECT ca.PartNumber,
   DENSE_RANK() OVER(ORDER BY m.PartNumberLHS,m.PartNumberRHS) AS UniqueNumber
FROM Matches m
INNER JOIN Grps g1 ON g1.PartNumber = m.PartNumberLHS
AND g1.GroupCount = m.MatchCount
INNER JOIN Grps g2 ON g2.PartNumber = m.PartNumberRHS
AND g2.GroupCount = m.MatchCount
CROSS APPLY (VALUES(m.PartNumberLHS),(m.PartNumberRHS)) ca(PartNumber)
)
SELECT p.PartNumber,
       p.PartValue,
       ISNULL(u.UniqueNumber,ISNULL(oa.MaxNum,0) + DENSE_RANK() OVER(ORDER BY p.PartNumber)) AS UniqueNumber
FROM #parts p
LEFT OUTER JOIN UniqueNumbers u ON u.PartNumber = p.PartNumber
OUTER APPLY (SELECT MAX(UniqueNumber) FROM UniqueNumbers) oa(MaxNum)
ORDER BY p.PartNumber,p.PartValue;

This reply was modified 2 years, 4 months ago by Mark Cowne.
This reply was modified 2 years, 4 months ago by Mark Cowne.

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

I'm in the same boat. Why would P4 have the same "unique" number as P3 and P5 have the same "unique" number as P2?? I'm just not seeing your "pattern" here.

--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)

DesNorton SSC-Insane Points: 24399 More actions · Answer 6

I stand to be corrected, but the pattern appears to be as follows

P1 is the only PartNumber that has PartValue in (1,2,3,4), therefor has its own UniqueNumber
P2 and P5 have PartNumber in (1,2), therefor have the same UniqueNumber
P3 and P4 have PartNumber in (1,2,3), therefore have the same UniqueNumber

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246980 More actions · Answer 7

Edit: changed my mind.

This reply was modified 2 years, 4 months ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 8

DesNorton wrote:

I stand to be corrected, but the pattern appears to be as follows
P1 is the only PartNumber that has PartValue in (1,2,3,4), therefor has its own UniqueNumber
P2 and P5 have PartNumber in (1,2), therefor have the same UniqueNumber
P3 and P4 have PartNumber in (1,2,3), therefore have the same UniqueNumber

Yes, that looks like it. But it wasn't obvious from the question.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Jonathan AC Roberts wrote:

DesNorton wrote:
I stand to be corrected, but the pattern appears to be as follows
P1 is the only PartNumber that has PartValue in (1,2,3,4), therefor has its own UniqueNumber
P2 and P5 have PartNumber in (1,2), therefor have the same UniqueNumber
P3 and P4 have PartNumber in (1,2,3), therefore have the same UniqueNumber
Yes, that looks like it. But it wasn't obvious from the question.

And, to ask the question, will there ever be groups that have "PartValues" of, say, 2,4,5 (for example) or will they always start at 1 and have NO missing numbers???

Also, I agree that posting the code is important but I wish people would also state the exact requirements instead of expecting us to derive it from code without test data on code that might actually not be working correctly. 🙁

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

ahmed_elbarbary.2010 wrote:

the goal mfrom asking question is to get result above without string aggreagte or comma separated suppose i have p1 1,2,3 p2 2,2,2 both p1 and p2 both have same count and same sum so are there are another solution without using string aggregate

You posted in a 2019 forum... what's wrong with using STRING_AGGREGATE ?

--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)

kaj SSCommitted Points: 1607 More actions · Answer 11

ahmed_elbarbary.2010 wrote:

I work on sql server 2019 i face issue i need to give unique number to every group of numbers without using string aggreagte or stuff
...
what i try
SELECT
p.PartNumber,
p.PartValue,
p2.Parts,
NewUniqueNumber = DENSE_RANK() OVER (ORDER BY p2.Parts)
FROM #parts p
JOIN (
SELECT
p2.PartNumber,
STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts
FROM #parts p2
GROUP BY
p2.PartNumber
) p2 ON p2.PartNumber = p.PartNumber;
it give me expected result but i don't need to use this logic
are there are another logic without using string aggregate or stuff
i need to use another logic depend on sum numbers or count it

Well, instead of using STRING_AGG you could try to use a sum power of two, which would do the same.

SELECT
    p.PartNumber,
    p.PartValue,
    p2.SumPartValue,
    DENSE_RANK() OVER (ORDER BY p2.sumPartValue) AS NewUniqueValue
FROM #parts p
JOIN (
SELECT
    PartNumber,
    SUM(CAST(PartValue AS BIGINT) ^2) AS sumPartValue
FROM #parts
GROUP BY
    PartNumber
) p2 ON p2.PartNumber = p.PartNumber
ORDER BY NewUniqueValue, PartNumber

Of course for this to work reliably it would rely on several requirements:

PartValue is always unique within each group of PartNumbers
PartValue is never bigger than 63 (bigint is 64 bits wide).

However, at least with the supplied test data it works, I think.

kaj SSCommitted Points: 1607 More actions · Answer 12

Sorry, I hereby withdraw my entry. I used a bit operator, not the power function, and that does work with the testdata, but is not a valid general solution. The power funktion would probably be okay, but it's only 32 bit apparently and thus of very little value.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 13

I still want to know why the OP wants to avoid the STRING_AGGREGATE() function.

--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)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

ahmed_elbarbary.2010 wrote:

the goal mfrom asking question is to get result above without string aggreagte or comma separated suppose i have
p1 1,2,3
p2 2,2,2
both p1 and p2 both have same count and same sum so are there are another solution without using string aggregate

I missed this. And that answers my other questions.

--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)