Tricky SQL Query Question

Question

Tricky SQL Query Question

Noc234234

Old Hand

Points: 335
More actions
July 15, 2009 at 2:28 pm

#95564

I've simplified this question but this is basically what I need to do. I need to order a series of numbers and names from a table, but there might be mutiples of the same name, in that case I want to order by the lowest number, but group the results together by name.
This example will explain it more clearly. This is the table:
1 John
2 Bill
3 Dave
4 John
5 Ed
6 Dave
I want my result to order by the lowest, but group them if a name occurs more than once, giving precedence to the lower number a name has. The result set should look like this:
1 John
4 John
2 Bill
3 Dave
6 Dave
5 Ed
Is this possible? I tried several combinations of ORDER BY and GROUP BY but I can't figure it out.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

attached please find a SQL2000 compliant solution. As far as I can see you need to use a subquery to get the order of the names...

Just out of curiosity: What's the business reason behind it?

use tempdb

-- define sample data

create table #t (id int, name varchar(30))

insert into #t

select 1 ,'John' union all

select 2 ,'Bill' union all

select 3 ,'Dave' union all

select 4 ,'John' union all

select 5 ,'Ed' union all

select 6 ,'Dave'

-- show sample data

select * from #t

-- select requested result using subquery (in SS2K5 I would have used a cte instead, that's why the alias "cte_names"...)

select t.id,t.name

from #t t

inner join (

select min(id) as cteID, name

from #t

group by name) cte_names

on t.name =cte_names.name

order by cte_names.cteID, t.id

/* results

idname

1John

4John

2Bill

3Dave

6Dave

5Ed

*/

--clean up

drop table #t

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 2

tnocella (7/15/2009)
I want my result to order by the lowest, but group them if a name occurs more than once, giving precedence to the lower number a name has. The result set should look like this:

Now that Lutz has given you a working example of how to do this, I've gotta ask... why did you need to do this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)