Incorrect syntax near the keyword 'Distinct'

Question

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`     

Solution 1

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?

Solution 2

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, '') 

Solution 3

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.

Solution 4

use

select distinct ItemId, ...

View more solutions

Comments

[M.Babcock]

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

[Ali Nouman]

Thanks for help i want to have output like when user select8x12 it then gets 1,5,9

[Matti Virkkunen]

@Hira: If you have a user selection, couldn't you just query for DISTINCT ItemId WHERE Size = @UserSelectedSize?

[Ali Nouman]

Thanks for help i want to have output like when user select8x12 it then gets 1,5,9

[Martin Smith]

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

[Ali Nouman]

thanks i got your idea thanks really for help.Thanks it will work for me.