SQL Server TOP(1) with distinct

Question

I am trying to extract the first row I get after ordering the result by i_version_id. If I do not use TOP(2), my query works as expected ans returns all results sorted by i_version_id. But when I add the TOP(2) (as shown below), it says that there is a syntax error near distinct. Please let me know what I am doing wrong.

SELECT TOP(2) 
    distinct(i_version_id) 
FROM 
    [PaymentGateway_2006].[dbo].[merchant] 
WHERE 
    dt_updated_datetime > '2013-11-11'
GROUP BY
    i_version_id 
ORDER BY 
    i_version_id;

Distinct isn't necessary when you're already grouping by the same column you're selecting. — Cᴏʀʏ, Nov 20 '13 at 02:36
Please show sample data and desired results. I'm trying to figure out how you are going to possibly get different results with and without the distinct, and I think you're going to need to provide more info. — Aaron Bertrand, Nov 20 '13 at 03:02

score 16 · Accepted Answer · edited Apr 23 '14 at 08:33

16

If you're only getting the TOP 1 then distinct is irrelevant. It's also irrelevant since grouping by the column will give you distinct values,

However, If you want more than one just remove the parentheses:

SELECT DISTINCT TOP(2) 
    i_version_id
FROM 
    [PaymentGateway_2006].[dbo].[merchant] 
WHERE 
    dt_updated_datetime > '2013-11-11'
GROUP BY
    i_version_id 
ORDER BY 
    i_version_id;

edited Apr 23 '14 at 08:33

Mike

827
11
27

answered Nov 20 '13 at 02:35

D Stanley

149,601
11
178
240

2

Yea, but let's say I need 2 distinct top results. – Darth.Vader Nov 20 '13 at 02:37
Can you provide some sample data – Juan Nov 20 '13 at 02:56

score 0 · Answer 2 · answered Nov 20 '13 at 03:54

Would this work?

SELECT
*
FROM
(
SELECT i_version_id,
    ROW_NUMBER() OVER (PARTITION BY i_version_id ASC) [counter]
FROM 
    [PaymentGateway_2006].[dbo].[merchant] 
WHERE 
    dt_updated_datetime > '2013-11-11'
GROUP BY
    i_version_id 
ORDER BY 
    i_version_id
) a
WHERE [counter] <= 2

This will give a row counter for each record. Using GROUP BY and DISTINCT in your example above is pointless as your GROUP BY is already restricting your records. Putting in the DISTINCT is just going to effect performance.

As for your error, you can't use TOP and DISTINCT together AFAIK. You could try this if you wanted too:

SELECT
TOP 2 i_version_id
FROM
(
SELECT i_version_id 
FROM 
    [PaymentGateway_2006].[dbo].[merchant] 
WHERE 
    dt_updated_datetime > '2013-11-11'
GROUP BY
    i_version_id 
ORDER BY 
    i_version_id
) a

(I haven't tested this as I don't have your Db, but I can't see why this wouldn't do what you need).

SQL Server TOP(1) with distinct

2 Answers2

Linked