C# SQL Server VIEW Query

I need to create a VIEW query...

For example:

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

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

Now, I need to order by the FIRST letter of the first word and the FIRST letter of the second word ascending and by Count descending...

The outcome should be:

Name                   Count
------------------------------
kraig Beno             333  
Kaganoff Benzion       122  
Mogrobejo Endika       555  
Mi开发者_如何学运维chelen Luis          656  
Van Gennep             443  

Lets see if you can :)

something like this query should work (I've set up 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

I'd go about this with a common 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 didn't see that Name was one column at first.

Unless I just don't understand your question... It is completely possible to split up a single column in the Order By clause while not effecting the output.

Example:

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

Maybe I'm just confused though, do you want to derive the count or? Regardless, output of this exactly matches your requested output.