SQL WHERE statement syntax error when using distinct count left

Question

I have a WHERE clause that logically I want it to bring back results where the first digit of the short names don't match.

The short names can be:

1.1
1.2
2.1
2.2

Sample data WITHOUT where clause:

+-----------+--------+------+
| Shortname | number |  ID  |
+-----------+--------+------+
| 2.1       |      1 | 3333 |
| 1.1       |     60 | 3333 |
| 1.2       |     90 | 3333 |
| 2.1       |     50 | 4444 |
| 2.2       |     30 | 4444 |
| 1.1       |     80 | 5555 |
| 1.2       |     10 | 5555 |
+-----------+--------+------+

Expected data WITH where clause:

+-----------+--------+------+
| Shortname | number |  ID  |
+-----------+--------+------+
| 2.1       |      1 | 3333 |
| 1.1       |     60 | 3333 |
| 1.2       |     90 | 3333 |
+-----------+--------+------+

I tried the code:

SELECT shortname, number, id
FROM table
WHERE ((left(shortname,1) like '%1%') != ((left(shortname,1) like '%2%')

But it generates the error:

Msg 102, Level 15, State 1, Line 21
Incorrect syntax near '!'.

Clarification UPDATE

I need the results per ID, so in the sample above there are ID's 3333, 4444 and 5555. I want to only bring back the ID 3333 because it doesn't have only a single first character value in each shortname. It contains both values 1 and 2.

Where as I don't want to see the other ID's as there short names are matching on the first digit 1 = 1 and 2 = 2 etc.

Answer 1

If you want to return the ID that a multiple first characters in the shortname, then first look at getting a distinct count of the rows:

select id
from yourtable
group by id
having count(distinct left(shortname, 1)) > 1;

This should return to you the rows that have both a 2 and a 1 as the first character when associated with the IDs. Then you can use this to return the rest of the data:

;with cte as
(
  select id
  from yourtable
  group by id
  having count(distinct left(shortname, 1)) > 1
)
select 
  t.shortname,
  t.number, 
  t.id
from yourtable t
inner join cte c
  on t.id = c.id;

See SQL Fiddle with Demo. This returns:

| SHORTNAME | NUMBER |   ID |
|-----------|--------|------|
|       2.1 |      1 | 3333 |
|       1.1 |     60 | 3333 |
|       1.2 |     90 | 3333 |

A more flexible option would be to get the characters before the decimal and verify that you have a distinct count of all the digits. To do this, you'll use a function like CHARINDEX along with the LEFT.

;with cte as
(
  select id
  from yourtable
  group by id
  having count(distinct left(shortname, charindex('.', shortname)-1)) > 1
)
select 
  t.shortname,
  t.number, 
  t.id
from yourtable t
inner join cte c
  on t.id = c.id;

See SQL Fiddle with Demo. This will return:

| SHORTNAME | NUMBER |   ID |
|-----------|--------|------|
|       2.1 |      1 | 3333 |
|       1.1 |     60 | 3333 |
|       1.2 |     90 | 3333 |
|      14.1 |      5 | 6666 |
|      14.2 |     78 | 6666 |
|      24.1 |     89 | 6666 |