|
Hi All, 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
add new comment
(comments are locked)
Why is there a TOP (1) without ORDER BY? You want exactly one row but you don't care which one? Once you add an ORDER BY to get predictable results, you can drop the DISTINCT. There is no difference - if you only return one row, what duplicate rows are you eliminating? (Of course that changes if you use WITH TIES and your ORDER BY leaves the possibility for ties.) There is ORDER BY Aaron. Sorry my bad. 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] |
