How to give same number if it exist on table or new number if it not exist based on part value? 1

[ahmedsa2018]

I work on sql server 2019 i have table #parts have group of numbers on Part Value Field .

my issue how to give unique number to every group of numbers on Part Value Field

if group numbers on part Value not exist before on table then it will take new number.

if group numbers on part Value exist on table it will give same number that exist per group on table

original table as below :

create table #parts
     (
     PartNumber varchar(50),
     PartValue int,
     UniqueNumber int
     )
     insert into #parts(PartNumber,PartValue,UniqueNumber,UniqueNumber)
     values
     ('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)

and IF add New Part it will get number 3 as it exist on original table

 ('P4',1,3),
 ('P4',2,3),
 ('P4',3,3)

and IF add New Part it will get number 2 as it exist on original table

('P5',1,2),
 ('P5',2,2)

and IF add New Part it will get number 4 as new number that consist (max numbers for original table)+ 1

('P6',1,4),
 ('P6',2,4),
 ('P6',3,4),
 ('P6',4,4),
 ('P6',5,4)

so please how to solve it without using stuff numbers or string aggregate ?

i don't need to use logic below because it will performance issue for big count

may be using string aggregte or stuff as 1,2,3 or 1,2 or 1,2,3,4 and give every group of numbers unqiue number .

to get different between every group

but i don't need to use this logic by stuff or string aggregte

 

[SkipVought]

A match is a match.

No match would be MAX(number) + 1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[ahmedsa2018]

to be clear
this is give me expected result
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;
so are there are another solution solve issue without using stuf or string aggreagte

 

[Chris Miller]

I don't see how that's a uniquenumber, even in the data you already have.

But you can have the same result without using STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) Parts by simply using COUNT(*):

SELECT
p.PartNumber,
p.PartValue,
p3.PartsAgg,
p3.PartsCount,
NewUniqueNumberAgg= DENSE_RANK() OVER (ORDER BY p3.PartsAgg),
NewUniqueNumberCount = DENSE_RANK() OVER (ORDER BY p3.PartsCount)
FROM #parts p1
JOIN (
SELECT
p2.PartNumber,
STRING_AGG(p2.PartValue, ',') WITHIN GROUP (ORDER BY p2.PartValue) PartsAgg,
Count(*) as PartsCount
FROM #parts p2
GROUP BY
p2.PartNumber
) p3 ON p3.PartNumber = p1.PartNumber;

This demonstrates both STRING_AGG and COUNT lead to the same DENSE_RANK(), at least with your sample data. And I'm 99% sure this won't change when the partnumbers are completely different, as the STRING_AGG values are ascending the same as count(*).

I fear, though, that the performance won't benefit much from that change. The need to know the DENSE_RANK() makes it a long operation, as it is an operation on all data.

Chriss

 

[Chris Miller]

First, answer in the same thread, don't start a new thread when your still on the same question

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

Where's that 4th column coming from? Your #parts table is defined with 3 columns: (PartNumber,PartValue,UniqueNumber). Also what "same sum"?

You can only get good answers if your outset is complete.

Chriss

 

[ahmedsa2018]

this row below
p1 1,2,3
is two columns only part number and partvalue separated comma
only for explain

 

[Chris Miller]

So you mean partnumber='p1 1',partvalue=2,uniquenumber=3

Okay, your data still doesn't make sense and is incomplete. As existing uniquenumbers have been computed using DENSE_RANK you have to have numbers 1 to N in the data.

A rank on partial data will always give a wrong result.

Chriss

 

[Chris Miller]

I don't think we get anywhere here. I can only give you the advice that any "next number" should be generated by a sequence.

https://www.sqlservertutorial.net/sql-server-basics/sql-server-sequence/

I'm aware sequences may not what you want, when numbering is per group or partition of data. But it works with multiple sequences, if not as SkipVaught already pointed out, a MAX()+1 of a group or partition would work instead.

Chriss

 

[Andrzejek]

Based on your original post, if that's your data in
[pre]
table: #parts
PartNumber PartValue UniqueNumber
P[blue]1[/blue] 1 [blue]1[/blue]
P[blue]1[/blue] 2 [blue]1[/blue]
P[blue]1[/blue] 3 [blue]1[/blue]
P[blue]1[/blue] 4 [blue]1[/blue]
P[red]2[/red] 1 [red]2[/red]
P[red]2[/red] 2 [red]2[/red]
P[green]3[/green] 1 [green]3[/green]
P[green]3[/green] 2 [green]3[/green]
P[green]3[/green] 3 [green]3[/green]
[/pre]

Are the PartNumber and UniqueNumber 'related'? Or that's just an accident?

It looks to me you need 2 tables (for normalized DB):[pre]
PartsA
PartID (PK) Description
[blue]1[/blue] P1 or whatever
[red]2[/red] P2 or something
[green]3[/green] P3 XYZ[/pre]

And[pre]
PartsB
P_ID (PK) PartID (FK) PartValue
1 [blue]1[/blue] 1
2 [blue]1[/blue] 2
3 [blue]1[/blue] 3
4 [blue]1[/blue] 4
... etc.
[/pre]


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson