Need help with a query
-
I have been trying to figure this out all day today but I can't seem to do it. Maybe someone here can help me.
The situation is as follows:
I have a table which contains records that can be sorted in alphabetical order. Each record in the table also has an id. Of course there are numerous records for each letter in the alphabet, but what I need to find is the id of the record that is alphabetically the last for that particular letter.
If this is confusing here's a quick example of what I'm looking for. In this example Custom is last sorted record for C's and the record that I am trying to locate.
C
Cat
Cave
Caviar
.
.
Custom
Any help would be much appreciated!!
-
Declare @A table (id int not null identity(1, 1), name varchar(50) not null primary key clustered)
Insert into @A (name) values ('atr')
Insert into @A (name) values ('d')
Insert into @A (name) values ('cr')
Insert into @A (name) values ('al')
Insert into @A (name) values ('ct')
Insert into @A (name) values ('cz')
Select * from @A
Select id from @A A inner join (Select left(name, 1) as Letter, max(name) as Name from @A group by left(name, 1)) dtNames on A.Name = dtNames.Name
id name
----------- --------------------------------------------------
4 al
1 atr
3 cr
5 ct
6 cz
2 d
id
-----------
1
6
2
-
Thank you!!!!!
-
NP... don't waste another day next time
.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply