SQL Server TOP(1) with distinct

Darth.Vader picture Darth.Vader · Nov 20, 2013 · Viewed 45.9k times · Source

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;

Answer

D Stanley picture D Stanley · Nov 20, 2013

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;