SQLTeam.com | Weblogs | Forums

How to avoid using string aggreagte for give unique numbers for every group of numbers?

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 :slight_smile:

 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

PartNumber PartValue UniqueNumber
P1 1 1
P1 2 1
P1 3 1
P1 4 1
P2 1 2
P2 2 2
P3 1 3
P3 2 3
P3 3 3
P4 1 3
P4 2 3
P4 3 3
P5 1 2
P5 2 2

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

Your results don't make any sense. Looks like you want to group by partnumber, do I don't understand the P4/P5 values in you expectations, Try this

Select partNumber, PartValue,

		 DENSE_RANK() OVER(Order By partNumber) as UniqueNumber
from #parts

@ahmedbarbary

On the other forum that you posted on, you posted in the 2019 forum so what's wrong with using STRING_AGGREGATE?

And I'm glad that Mike also missed the requirements that you didn't state. Posting the code is important but we shouldn't have to dissect the code to get the problem definition. I'm not going to state the grouping you want here... YOU state it. It's your problem? :wink:

1 Like

Why are P4's unique number set to 3 - and P5's unique number set to 2? How is that determined?

Because 2 and 5 both have 1 and 2 as values. Now, how hard would it have been for the OP to just come out and say that?

I for one appreciate all of you guys' charitable work in his behalf. I admire your patience. all of you @JeffModen @jeffw8713 @mike01

@yosiasz - I can't tell if you're being sincere or sarcastic. Since I can't tell, I'll explain and hope you were being sincere...

This particular user is known on this and several other forums for providing partial requirements and not answering questions.

Here's what the OP ultimately posted on the other forum as an additional explanation...

the goal mfrom [sic] 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

My question to everyone is, even with the example data AND the query he provided, would YOU have guessed that desired grouping is based on "both the same count and same sum" as the actual requirement? I think not. My impatience here is that he DID post that as a separate post on the other forum but he did not post it here. And, that requirement is also the reason why he doesn't want to use STRING_AGGREGATE()... it creates the wrong answer. And, none of the test data uses the 2,2,2 example he gave later and there's no way to even guess that from the original post.

And, finally, the OP never came back to this thread to say that he'd already gotten a solution and post the solution.

How can you have any patience with that? Because this OP is basically a "Good Man" at heart and a fellow "database warrior", we're trying to teach this good man some forum etiquette because a whole lot of people don't have such patience (and I've been PM'd a couple of times about it) and simply refuse to try to help him unless he changes his ways. :wink:

For those that are interested, you can find one solution at the following URL...
https://www.sqlservercentral.com/forums/topic/how-to-give-unique-number-to-every-different-group-of-numbers/page/2#post-4068160

To prevent any future "site rot", here's the test data I posted there...

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

And here's the solution I posted...

   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
;

And here are the results... Do notice how P6 is a part of Group 2 (Inique Number) because, like P3, it has a count of 3 and the 3 counts sum up to 6 for both groups.

1 Like

p.s. The OP also never responded with which of the many coded answers there were on the other site did what he wanted. That's also frustrating. :frowning:

you are surprised?

No. Bringing it up with hopes the OP will read it and start to practice a bit of forum etiquette so that I don't become one of the many.

I doubt OP ever will. History of OP's posts all over different forums proves me right. OP seems entitled. why shouldn't they. people are doing their work, for free, all over different forums. log in, grab task, grab coffee & donut (éclair cause they are light and fluffy), post on all the forums, fix up the title so as not to get tracked by people google searching the title to point them to already provided answer. kick back, come back in a bit, copy pasta answer, resolve ticket, get paid $$$. why waste time responding if it worked, not even a thank you. for the last 2+ years.

OP is boss, :money_with_wings:

I should maybe try fiction writing

1 Like