CODEWITHSUNDEEP
sqlsql-server
hanahouhanah
hanahouhanah

Reputation: 31

Get Distinct First Character, Return Entire Column

I have a column called "Majors" and I want to get everything from that column, but filter out anything that has a duplicate first character. Such as if I had AMST and ADIB, only one of them would show up, where it can be indeterminate.

So if I had AMST, ADIB, BIOL, CSCI, CENG, EENG, then the end result might be AMST, BIOL, CENG, EENG.

What I currently have is: 

DECLARE @VTest table(majName varchar(100))

INSERT INTO @VTest (majName) 
SELECT DISTINCT TOP 5 Major FROM MajorTbl ORDER BY Major desc


--For some reason returns all the rows despite it only giving distincts (Would return AMST and ADIB, even though it should only give one of those and not both)
SELECT * FROM @VTest
WHERE SUBSTRING(majName, 1, 1) =
     (SELECT DISTINCT SUBSTRING(CONVERT(varchar(100), [@VTest].majName),  1, 1) as c)

Any ideas on how I could get the output I want?

Upvotes: 0

Views: 60

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271171

If you want only one row per "something", then row_number() seems like the obvious candidate for solving the problem:

select t.*
from (select t.*,
             row_number() over (partition by left(majName, 1) order by newid()) as seqnum
      from @VTest t
     ) t
where seqnum = 1;

Note that this uses SQL Server conventions for things like randomizing the row. Your code looks like SQL Server code.

Upvotes: 2

Related Questions