CODEWITHSUNDEEP
c#sql-serversql-server-2005
Ali Nouman
Ali Nouman

Reputation: 3412

Incorrect syntax near the keyword 'Distinct'

I am getting following error in my query System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Distinct'. I have searched the Google but didn't find any helpful solution according to my query.My query is this

SqlCommand myCommand = new SqlCommand("select ItemId,Distinct Size from ItemTilesSizes ",
                                                     myConnection);

My table name is ItemTilesSizes and it has two columns

ItemId  Size

1   8x13      
1   8x12      
5   8x10      
5   8x12      
5   8x13      
8   10x10     
8   4x4       
9   8x12      
14  8x13      
15  8x10      
15  24x24`

Upvotes: 10

Views: 20672

Answers (4)

Vimal bhatt
Vimal bhatt

Reputation: 285

The SQL DISTINCT command used along with the SELECT keyword retrieves only unique data entries depending on the column list you have specified after it. so you have to use DISTINCT command like following in your select query

"select DISTINCT ItemId,Size from ItemTilesSizes"

This will select unique records from your table in a combination of both ItemID and Size.

Upvotes: 3

Martin Smith
Martin Smith

Reputation: 453766

Distinct needs to go immediately after the SELECT

SELECT DISTINCT ItemId,
                Size
FROM   ItemTilesSizes

If you wanted it to apply only to Size you need GROUP BY and an aggregate to define which of the possible matching ItemId values should be returned (the below example returns the largest)

SELECT MAX(ItemId) AS ItemId,
       Size
FROM   ItemTilesSizes
GROUP  BY Size

Although from the clarification in the comment I'd just bring that back as a single column resultset and do any required concatenation in your application. If you must do it in SQL you can use XML PATH

SELECT STUFF((SELECT ',' + LEFT(ItemId, 10)
              FROM   ItemTilesSizes
              WHERE  Size = '8x12'
              FOR XML PATH('')), 1, 1, '')

Upvotes: 6

Matti Virkkunen
Matti Virkkunen

Reputation: 65166

DISTINCT has to come first, but it'll get you all distinct pairs of ItemId and Size. Is that what you want or were you looking for something else?

Upvotes: 16

juergen d
juergen d

Reputation: 204894

use

select distinct ItemId, ...

Upvotes: 2

Related Questions