How to find unique values in a table

Question

How to find unique values in a table

Viewing 4 posts - 16 through 18 (of 18 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

It's all good! Not sure if I've been mistaken for Pietlinden before, but I know my opinions on query hints are that they should be avoided.

And I agree about getting a good clustered index key to help with blocking and deadlocks. Sometimes though, you inherit the system or it is a 3rd party tool and changes like that put you out of support, so you are stuck living with what you got. And sometimes the Sr DBA watched a presentation where they heard that a Clustered index key should be numeric and ever increasing and they thought "Hey, that's an IDENTITY!" and now all clustered index keys are IDENTITY's.

I think there ARE cases where an IDENTITY is the right choice, but if MOST of your SELECTs aren't filtering (or sorting) by the IDENTITY value, you have picked the wrong key. And sometimes to fix picking the wrong key, a nonclustered index needs to be created. But this is more of a "band-aid" fix to the problem.

I'm still not a huge fan of query hints in general as I have found that once you find a hint that helped one query, it is FAR too easy to think it'll fix ALL queries. I've seen abuse of NOLOCK, RECOMPILE, OPTIMIZE FOR, HASH JOIN... sometimes it is best to leave it to the optimizer to do its magic. Another reason why I like to have comments surrounding query hints is that it can be useful when doing upgrade testing. When we change from 2016 to 2019 (for example), do we still need that query hint OR did a change in the CE help that query and we can remove the hint? It is a thing we can test.

When I ask for those types of comments, I'm not looking for a novel explaining why NOLOCK was used, I am looking for things like "Support ticket 12345 - excessive blocking was noticed on table ABC and we needed a quick fix to get production back up and running - BG". Explains what was fixed, why it was fixed, and who fixed it.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

Mond wrote:

Hello trying to find unique values on the following table
First temporary table query (Master Data)
insert into #tempCOO select sk.CountryOfOrigin,im.partnumber,sk.Quantity,sol.id,lp.id,lpl.id,so.id,p.SerialNumber FROM sk join lpl on sk.LoadPlanLineId = lpl.id join lp on lp.id = lpl.LoadPlanId join sol(nolock) on sol.id=lpl.SalesOrderLineId join so on so.id=sol.SalesOrderId join im on im.id = sol.itemmasterid join pd on pd.content = sk.batchnumber and Pd.PackageAttributeId=37 join p on p.id=pd.PackageId where lp.Loadnumber= 'L001' order by p.SerialNumber desc
The result is the following:
I need to find unique values for a combination of country of Origin and Part Number
These are the values I expect
Could you help me to write the querys I need?
Thanks so much

SELECT CountryOfOrigin, 
       PartNumber, 
       SUM(Quantity) Qty,
       'x' Sol,
       'x' LoadPlanId,
       'x' SalesOrder,
       'x' PalletNumber
  FROM #tempCOO
 GROUP BY CountryOfOrigin, PartNumber
 ORDER BY CountryOfOrigin, PartNumber

Mond Old Hand Points: 356 More actions · Answer 3

This works super good:

SELECT CountryOfOrigin,

PartNumber,

SUM(Quantity) Qty,

'x' Sol,

'x' LoadPlanId,

'x' SalesOrder,

'x' PalletNumber

FROM #tempCOO

GROUP BY CountryOfOrigin, PartNumber

ORDER BY CountryOfOrigin, PartNumber

thanks a lot!!!!

Mond Old Hand Points: 356 More actions · Answer 4

Mond

Old Hand

Points: 356

May 7, 2021 at 1:15 am

#3880367

thank you so much!!