DISTINCT OPERATOR
Is there a better way to rewrite below code without using DISTINCT operator. Other thing, is that I don't see any duplicates inside the table. But not sure in future if we get duplicate combination. that could be reason why some developer has written such type of code. Can we re-write this code in a better way??
select distinct top (1)
[x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE [x].[SampleStatusID] = 1
Thank you.
M
SELECT DISTINCT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)
–AND ([x].[ComponentID] = @__componentId_0)
ORDER BY [x].[DateDrawn] DESC
,[x].[EquipmentLife] DESC
,[x].[LubricantLife] DESC
,[x].[DateRegistered] DESC
,[x].[SampleID]
WITHOUT DISTINCT = 50%
WITH DISTINCT = 50%
Don't know the reason why 50-50!!
I thought with distinct will be little expensive? Why is it so? attaching plan "distinct.sqlplan"
SELECT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)
SELECT DISTINCT TOP (1) [x].[DateDrawn]
,[x].[EquipmentLife]
,[x].[LubricantLife]
,[x].[DateRegistered]
,[x].[SampleID]
FROM [LubeAnalyst].[Sample] AS [x]
WHERE ([x].[SampleStatusID] = 1)