Can I do this with group/order by? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Can I do this with group/order by?

Author

Topic

interclubs
Yak Posting Veteran

63 Posts

Posted - 2004-01-12 : 10:17:18

I have the following Table:

ID|InviteID|LName

The data in it would look like:

1|2|Smith
2|4|Jones
3|7|Michales
4|7|Warner

What I would like to do it select the information from the table, ordered by the LName field, but I would like them grouped by the InviteID.... So if I were to do the select, this is what I would want the data to look like:

Jones
Michales
Warner (This would be after Michales because they share the same InviteID)
Smith

Is this possible via something simple, or am I just dreaming?

Thanks!

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 10:22:49

What I'm getting...

I want to order by last name...and I don't care about the order of the invite id...until there's a group of invite id's, then I do, but then the order of the last names don't count...

About right?

Brett

8-)

interclubs
Yak Posting Veteran

63 Posts

Posted - 2004-01-12 : 10:28:59

Yeah, I would say thats pretty much right on.

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 10:32:15

I'll think about it....but right now, I have to clean up my desk...

My optimizer just threw up.....

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-01-12 : 14:16:35

My monday's linguistic drool:

create table #t (n int, f varchar(8))
insert into #t
select 2, 'Smith' union
select 4, 'Jones' union
select 5, 'Adame' union
select 7, 'Micha' union
select 7, 'Warne'

select * from #t
order by
(select count(*) from #t t where
t.f<(select min(tt.f) from #t tt where tt.n=#t.n)), f
(select min(tt.f) from #t tt where tt.n=#t.n), f

-- PS This shocking improvement is NOT mine.... :(

drop table #t

X002548
Not Just a Number

15586 Posts

Posted - 2004-01-12 : 14:21:35

DAMN

This is some good sheet man...want a hit?


create table #t (n int, f varchar(8))
insert into #t
select 2, 'Smith' union
select 4, 'Jones' union
select 5, 'Adame' union
select 7, 'Micha' union
select 7, 'Warne' union
select 1, 'Rob'   union
select 2, 'Stoad' union
select 8, 'Tara' union
select 8, 'Brett' union
select 10, 'Aardvark' union
select 0, 'Zeus'


select * from #t
order by
(select count(*) from #t t where
t.f<(select min(tt.f) from #t tt where tt.n=#t.n)), f

drop table #t

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources