SQL Server : find non duplicates

Question

I have a table in SQL Server that looks like this:

id   filename   size
---------------------
1    abc.aaa    0
2    abc.zip    123
3    def.aaa    0
4    def.zip    124
5    ghi.zip    123
6    jkl.aaa    0
7    jkl.zip    124
8    mno.zip    234
9    pqr.aaa    0
10   pqr.zip    345

I need to find the rows with the unique file name "without" the extension.

So the result should be

id   filename   size
---------------------
4    def.zip    124
8    mno.zip    234

I've tried using

select distinct (Left(filename, LEN(filename) - 4 
from tbl 

but that returns abc, def, ghi, jkl etc...

How can I get my desired result?

Answer 1

With NOT EXISTS:

select t.* from tablename t
where not exists (
  select 1 from tablename
  where id <> t.id 
  and left(filename, charindex('.', filename)) = left(t.filename, charindex('.', t.filename))  
)

or COUNT() window function:

select t.id, t.filename, t.size
from (
  select *, count(1) over (partition by left(filename, charindex('.', filename))) counter
  from tablename
) t
where t.counter = 1

See the demo.
Results:

> id | filename | size
> -: | :------- | ---:
>  5 | ghi.zip  |  123
>  8 | mno.zip  |  234