Practice Activity - remove duplicate rows in SQL Server (three different ways)

[@jacekk7819]

Super practice activity Philip as always! Its worth mentioning that numbers (results) may be different depending on what database we will create our view because sys.objects can have different numbers of objects inside. Here is my query before I watch this video, some of them are super silly but the result is ok I mean without duplicates :-
- to see duplicates
select count(object_id)as [Nr]
,object_id
FROM WithDuplicates
group by object_id
having count(object_id) >1
order by Nr desc
-- to exclude duplicates
SELECT
distinct(object_id) , [name]
FROM WithDuplicates
ORDER BY object_id
-- to exclude duplicates
SELECT object_id, [name]
FROM WithDuplicates
intersect
SELECT object_id, [name]
FROM WithDuplicates
ORDER BY object_id ;
-- to exclude duplicates
WITH CTE_duplicates as (
select object_id, [name]
, ROW_NUMBER () over (partition by object_id order by name) as [Dup]
FROM WithDuplicates
) select object_id, [name] from CTE_duplicates where dup = 1
-- to exclude duplicates
select object_id, [name]
,GROUPING_ID (object_id, [name])
FROM WithDuplicates
group by object_id, [name]
ORDER BY object_id
-- to exclude duplicates
drop table if exists #temp_result
select object_id, [name]
into #temp_result
FROM WithDuplicates
group by rollup( object_id, [name])
ORDER BY object_id
select * from #temp_result
where name is not null
;
-- to exclude duplicates
drop table if exists #temp2 ;
with cte_X as (
select object_id, [name]
,lead (object_id , 2) over (order by [name] desc) as [lead]
FROM WithDuplicates
) select distinct(lead) ,object_id, [name]
into #temp2
from cte_X
select object_id, [name] from #temp2
ORDER BY object_id

[@SQLServer101]

Hi Jacekk7819. Wow - that's an overall lots of ways. Well done. The key is to have all of these ways in your mind, and then when another problem happens, you have all of these tools at your disposal to solve it. That's great. Phillip

[@BeyalaBaDingo]

I know this an old video, but i love how you go into the why? Could you possibly make more videos regarding this?

[@SQLServer101]

Hello Beyala. What “why” are you referring to? Phillip

[@bravucod]

Great Video!

[@Mayank-jw9yy]

Can we get RAW DATA used in video....
It will be very beneficial if u can share it with us.

[@SaynChuldum]

Ну ты и колдун парень 😮

[@marcoskapanda]

Your explanation is hard to follow. For a learner, it's impossible to keep track of what you're saying.

[@SQLServer101]

Hi User. It's a tricky subject, and may be at a more advanced level than you currently are. However, I would urge you, if you want to get to have a higher level, to get some training, whether it is by video course or book form. Keep learning! Phillip

[@marcoskapanda]

@SQLServer101 Thank you for your feedback. What book would you recommend?

[@SQLServer101]

Hi User. I would have a look at "Querying Microsoft SQL Server 2012 Exam 70-461 Training Kit". It's very cheap on the Kindle version. Phillip