3

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  
Michelen Luis          656  
Van Gennep             443

Lets see if you can :)

Improve this question
8
  • 1
    If you're counting the names, each unique name will appear only once in the list and the requirement to sort by count descending after sorting by name becomes moot as it would only be relevant as a tie breaker. Commented Jan 12, 2011 at 15:05
  • hi Joe, thanks for the reply. i think that grouping is needed because after sorting the names, one would need to group the names with the same starting letters and sort them descending... Commented Jan 12, 2011 at 15:12
  • What does your source table look like? Does that have forenames and surnames split out? Commented Jan 12, 2011 at 15:19
  • no. only a space between them... Commented Jan 12, 2011 at 15:30
  • 1
    First fix your data structure, you may have only two word names now, there is no guarantee that will continue. If you want to sort on separate names, they should be parsed to separate fields on insert. It is a bad choice to do this parsing at the time you run the selct queries. Commented Jan 12, 2011 at 15:38

3 Answers 3

Reset to default
3

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Should just state that I am working on the assumption that you have a table with two columns called 'Name' and 'Count' and that 'Count' is not an aggregated value.
you are right. but this is the result i'm getting... Kaganoff Benzion 122 Van Gennep 443 Michelen Luis 656 kraig Beno 333 Mogrobejo Endika 555
@joy, openshac's query works for me, are you using a case sensitive database or some strange collation that would affect ordering?
Have you run the actual SQL I posted? When I run it I get: kraig Beno 333 Kaganoff Benzion 122 Mogrobejo Endika 555 Michelen Luis 656 Van Gennep 443 Maybe you having quite copied something correctly?
thanks man! for your help and everything. my mistake...wonderful! i tried it a lot myself and couldn't... Geniuses are all over here... i bow down :) thanks!
2

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.

Improve this answer

1 Comment

I didn't go this route as the output requested seems to me to be an unchanged Column selection (ie [Name], [Count] in and [Name], [Count] out) that said if you need to perform a more complicated query a CTE in my opinion is the way to go and this is a good example of a basic one. +1
0

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.

Improve this answer

2 Comments

You probably want:Order By ... [Count] DESC
Many thanks, appreciated. Advice: Heed HLGEMs warning, having a column with two sets of data stored in it is just asking for problems if you ever need to access just one (ie FirstName + LastName = Name). Now is the time to fix this because it will only get harder to convert later down the road. Additionally your query is easier then too: Select FirstName, LastName, [Count] from YourTable Order By Substring(FirstName, 1, 1), Substring(LastName, 1, 1), [Count] DESC (OR nix the substring and just let SQL do it's job ordering First/Last alphabetically)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.