DISTINCT OPERATOR

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

avatar image By Maahi 1 asked Dec 21, 2017 at 08:41 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Dec 21, 2017 at 08:44 PM

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.)

avatar image Maahi Dec 21, 2017 at 08:54 PM

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]

avatar image Aaron Bertrand ♦ Dec 21, 2017 at 08:57 PM +

Did you compare query results with/without DISTINCT?

10|10000 characters needed characters left

1 answer: sort voted first
distinct.sqlplan (46.7 kB)
avatar image By Maahi 1 answered Dec 21, 2017 at 09:05 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x9
x4
x2
x2

asked: Dec 21, 2017 at 08:41 PM

Seen: 19 times

Last Updated: Dec 21, 2017 at 09:17 PM