May 2, 2016 at 1:50 pm
Hi All, I'm fairly new to TSQL world. I have a requirement to remove recitative records from result set.
I've attached the result set. I need to remove duplicates of Ser.Servername in the result set. Can someone please help me on this.
My code
select
ROW_NUMBER() OVER(ORDER BY ser.servername ASC) AS RowID,
Usage.UsageTypeName,
IT.InstanceTypeName,
I.InstanceName,
ser.Servername,
cust.customername,
dc.datacentername,
st.servertypename
from [dbo].[InstanceTypes] IT
join [dbo].[Instances] I on IT.InstanceTypeID = I.InstanceTypeID
join [dbo].[InstanceServers] ITS on ITS.InstanceID = I.InstanceID
join Servers Ser on Ser.ServerID = ITS.ServerID
Join [dbo].[UsageTypes] Usage on Ser.UsagetypeID = Usage.UsageTypeID
join [dbo].[DataCenters] DC on DC.DataCenterID = Ser.DataCenterID
join [dbo].[ServerTypes] ST on ST.ServerTypeId = Ser.ServerTypeId
join [dbo].[Customers] cust on cust.customerid = I.customerid
where I.Active = 1 and usagetypename != 'Decommissioned'
and InstanceTypeName = 'Perforce'
order by ROW_NUMBER() OVER(ORDER BY ser.servername ASC)
May 2, 2016 at 1:54 pm
I'm not sure what you are asking. I wouldn't class those records as duplicates. What is it you are trying to achieve.
May 2, 2016 at 2:20 pm
I want remove all the duplicate entries of Ser.Servername and keep only unique values of Ser.Servername. I attached the Desired_Result_Set
It does not matter what values come up in other columns like IT.InstanceName and Cust.CustomerName.
May 2, 2016 at 2:36 pm
I'm heading out of the office, but the solution is the same as the one I posted on this thread: http://www.sqlservercentral.com/Forums/Topic1782605-3077-1.aspx
May 2, 2016 at 3:37 pm
Thanks a million for differing me to the old thread. I got the answer from there .
WITH Matt_RemoveDuplicateServers AS
(
select
ROW_NUMBER() OVER(PARTITION BY ser.servername ORDER BY ser.servername DESC) AS RowID,
Usage.UsageTypeName,
IT.InstanceTypeName,
I.InstanceName,
ser.servername,
cust.customername,
dc.datacentername,
st.servertypename
from [dbo].[InstanceTypes] IT
join [dbo].[Instances] I on IT.InstanceTypeID = I.InstanceTypeID
join [dbo].[InstanceServers] ITS on ITS.InstanceID = I.InstanceID
join Servers Ser on Ser.ServerID = ITS.ServerID
Join [dbo].[UsageTypes] Usage on Ser.UsagetypeID = Usage.UsageTypeID
join [dbo].[DataCenters] DC on DC.DataCenterID = Ser.DataCenterID
join [dbo].[ServerTypes] ST on ST.ServerTypeId = Ser.ServerTypeId
join [dbo].[Customers] cust on cust.customerid = I.customerid
where I.Active = 1 and usagetypename != 'Decommissioned' and InstanceTypeName = 'Perforce'
)
select * from Matt_RemoveDuplicateServers where RowID = 1
May 3, 2016 at 6:40 am
Not too be picky but if your requirement of the other columns is not needed than what you ask can be accomplished really easy..
SELECT ServerName FROM InstanceTypes GROUP BY ServerName
I'm sorry but I really feel strongly on this point, especially in an IT world such as ours where there are a thousand different ways to accomplish a task. The simplest solution is always the best one.
EDIT
After re-reading your post. Im now thinking you wanted to remove duplicates in the tsql resultset you posted, in which case, only choosing the first row of your ROW_NUMBER column is the best and easiest solution.... Sorry for reading too much into what you were looking for....:)
May 3, 2016 at 6:56 am
Mac1986 (5/2/2016)
Thanks a million for differing me to the old thread. I got the answer from there .
ROW_NUMBER() OVER(PARTITION BY ser.servername ORDER BY ser.servername DESC) AS RowID,
Just a small note. Your RowID is not guaranteed to assign the row numbers the same way every time. I'm not sure if you could assign a significant way to order the rows.
Otherwise, I'm glad to have been of some help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply