CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
kingrichard2005
kingrichard2005

Reputation: 7289

TSQL Distinct, and OrderBy and WHERE

I have the following SQL statement sample:

SELECT DISTINCT [CommentNdx]
      ,[CommentsText]
      ,[DateTimeAdded]
  FROM [dbo].[CommentTable]
ORDER BY [dbo].[CommentTable].DateTimeStart DESC
WHERE [CommentsText] = 'Hello World'

I keep getting the error Incorrect syntax near the keyword 'WHERE'. I know the syntax is incorrect but I'm not sure how this should be formatted. Any help is appreciated.

UPDATE:

My mistake, I meant date time start should be datetimeadded. Corrected syntax.

SELECT DISTINCT [TestCommentNdx]
      ,[TestID]
      ,[CommentsText]
      ,[DateTimeAdded]
      ,[OperatorNdx]
  FROM [PTDB].[dbo].[TestsComments]
WHERE [TestID] = 1174411854
ORDER BY [PTDB].[dbo].[TestsComments].[DateTimeAdded] DESC

UPDATE 2:

Thanks much everyone, one last thing, would it make a difference if there were joins in the select statement? I have a really long query with joins and when I try to use DISTINCT, I get ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Upvotes: 1

Views: 224

Answers (2)

user596075
user596075

Reputation: 

SELECT DISTINCT [CommentNdx] 
      ,[CommentsText] 
      ,[DateTimeAdded] 
  FROM [dbo].[CommentTable]  
WHERE [CommentsText] = 'Hello World' 
ORDER BY [dbo].[CommentTable].DateTimeStart DESC -- you can't do this

ORDER BY follows the WHERE clause.

EDIT: As per @LukeGirvin post, you can't sort by a column that isn't included in the SELECT clause.

Upvotes: 2

Luke Girvin
Luke Girvin

Reputation: 13452

The WHERE needs to come before the ORDER BY. Also, you won't be able to sort by DateTimeStart unless it's included in the SELECT statement.

Upvotes: 10

Related Questions