SQL Server Error: Incorrect syntax near the keyword Distinct

nedash

SOLVED CLOSED ACTIVE ANSWERED
User: nedash
Posted: on Oct 16, 2019 01:40 AM
Forum: SQL Server
Answer: 1
Views: 15202

I wrote query in sqland I want it select data from database that doesnt show repeated number from Mobile column below is code

SELECT ROW_NUMBER() OVER
(
    ORDER BY [name] asc
)AS RowNumber
,id
,name
,Email
,Distinct Mobile2 
,PhoneHome1
INTO #Results
FROM PhoneBook

but below error accure:

Msg 156, Level 15, State 1, Procedure phone_Information, Line 16 Incorrect syntax near the keyword 'Distinct'.

best regards

Neda

dharmend...

0 1

Reply
ANSWER
User: dharmendr
Replied: on Oct 16, 2019 02:13 AM
Modified: on Nov 04, 2019 02:06 AM
Report

Hi nedash,

With all the columns it is not possible to display distinct rows.

Distinct needs to go immediately after the SELECT.

DISTINCT applies to all the selected fields not to an individual field.

You have to write like below.

SELECT DISTINCT ROW_NUMBER() OVER
(
    ORDER BY ContactName ASC
) AS RowNumber     
,CustomerID
,ContactName
   
INTO #Results
FROM Customers

SELECT * FROM #Results

DROP TABLE #Results

Since you are using row_number function there is no use of DISTINCT function.

SQL Server Error: Incorrect syntax near the keyword Distinct

Company

Explore

Follow us

Contact Us