C # SQL Server VIEW query

Question

I need to create a VIEW request ...

For instance:

Name                   Count
------------------------------
Kaganoff Benzion       122  
Van Gennep             443  
Michelen Luis          656  
kraig Beno             333  
Mogrobejo Endika       555  

* All names in the "Name" column containing two words with a space between them.

Now I need to order by the first letter of the first word and the first letter of the second word in ascending order and in the column that goes down ...

The result should be:

Name                   Count
------------------------------
kraig Beno             333  
Kaganoff Benzion       122  
Mogrobejo Endika       555  
Michelen Luis          656  
Van Gennep             443  

Let's see if you can :)

Answer 1

something like this query should work (I created my own temp table with your data)

create table #Temp (Name varchar(100), [Count] int)
insert into #Temp (Name, [Count]) VALUES ('Kaganoff Benzion', 122)
insert into #Temp (Name, [Count]) VALUES ('Van Gennep', 443)
insert into #Temp (Name, [Count]) VALUES ('Michelen Luis', 656)
insert into #Temp (Name, [Count]) VALUES ('kraig Beno', 333)
insert into #Temp (Name, [Count]) VALUES ('Mogrobejo Endika', 555)

select
SUBSTRING(Name, 1, PATINDEX('% %', Name)) AS FirstName,
SUBSTRING(Name, PATINDEX('% %', Name) + 1, LEN(Name) - PATINDEX('% %', Name)) AS SecondName,
[Count]
from #Temp
ORDER BY SUBSTRING(Name, 1, 1), SUBSTRING(Name, PATINDEX('% %', Name) + 1, 1), [Count] DESC

drop table #Temp

Answer 2

I would do this with a regular table expression.

DECLARE @data TABLE (Name varchar(50), NameCount int);

INSERT INTO @data (Name, NameCount)
SELECT 'Kaganoff Benzion', 122
UNION SELECT 'Van Gennep', 443
UNION SELECT 'Michelen Luis', 656
UNION SELECT 'kraig Beno', 333
UNION SELECT 'Mogrobejo Endika', 555;

--Now that we have the data setup, use a CTE...

WITH NamesAndLetters AS
(
    SELECT 
          SUBSTRING(UPPER(Name), 1, 1) [FirstNameLetter]
        , SUBSTRING(UPPER(Name), PATINDEX('% %', Name) + 1, 1) [LastNameLetter]
        , Name
        , NameCount
    FROM @data
)
SELECT Name, NameCount
FROM NamesAndLetters 
ORDER BY 
      FirstNameLetter ASC
    , LastNameLetter ASC
    , NameCount DESC

Sorry for the first post ... I did not see the Name first be a single column.

Answer 3

... Order By, .

:

Select [Name], [Count]
from [YourView]
Order By 
    Substring([Name], 1, 1) , 
    SUBSTRING([Name], PATINDEX('% %', [Name]) + 1, PATINDEX('% %', [Name]) + 2), 
    [Count] Desc

, , ? , .