Help with ordering - SQL Server Forums

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

SQL Server Development (2000)

Help with ordering

Author

Topic

derketo
Starting Member

28 Posts

Posted - 2005-01-31 : 01:20:07

I know this probably has a simple solution...I just can't seem to figure it out.

I need to group some results together then order by a completely different field.

Here's the result set I need.

Username DateSent
abc123 12/1/2004
abc123 12/15/2004
xyz123 12/5/2004
xyz123 12/18/2004

Order by date then username grouped...anyone?

Thanks so much in advance.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-31 : 01:23:45

I guess I'm confused on this one. What you displayed would just be a simple ORDER BY Username, DateSent. Post ddl, dml, and a sample resultset. Maybe we'll understand a little better what you want. Or, maybe I'm just slow because it's late, and I should be sleeping.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

derketo
Starting Member

28 Posts

Posted - 2005-01-31 : 01:29:13

Sorry...switch it around.

Username DateSent
xyz123 12/1/2004
xyz123 12/15/2004
abc123 12/5/2004
abc123 12/18/2004
def123 12/8/2004
def123 12/9/2004

Has to sort by datesent then by username grouped.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-31 : 02:16:19

will this do?
select Username, DateSent
from MyTable
group by Username, DateSent
order by DateSent, Username

Go with the flow & have fun! Else fight the flow

derketo
Starting Member

28 Posts

Posted - 2005-01-31 : 02:22:22

Didn't work. That just orders it by datesent. I actually need it ordered by datesent and grouped by username.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-31 : 03:48:36

there's probably a better way than this...


declare @t table (UserName varchar(50), DateSent datetime)
insert into @t
select 'xyz123', '12/1/2004' union all
select 'xyz123', '12/28/2004' union all
select 'abc123', '12/5/2004' union all
select 'def123', '12/8/2004' union all
select 'abc123', '12/18/2004' union all
select 'xyz123', '12/15/2004' union all
select 'def123', '12/9/2004'


select 	username, datesent
from (select isnull((select username + cast(count(*) as varchar(3))
	from @t where datesent < t1.datesent and username = t1.username
	group by username), username + '0') as DateRank,	
	datesent, username 
from 	@t t1
) t2
order by DateRank desc

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-31 : 04:56:23

Modified code of spirit1

declare @t table (UserName varchar(50), DateSent datetime)
insert into @t
select 'xyz123', '12/1/2004' union all
select 'xyz123', '12/28/2004' union all
select 'abc123', '12/5/2004' union all
select 'def123', '12/8/2004' union all
select 'abc123', '12/18/2004' union all
select 'xyz123', '12/15/2004' union all
select 'def123', '12/9/2004'

Select * from @t group by UserName ,Datesent

The result is

UserName DateSent
abc123 2004-12-05 00:00:00.000
abc123 2004-12-18 00:00:00.000
def123 2004-12-08 00:00:00.000
def123 2004-12-09 00:00:00.000
xyz123 2004-12-01 00:00:00.000
xyz123 2004-12-15 00:00:00.000
xyz123 2004-12-28 00:00:00.000

Which is orderd by Datesent and grouped by UserName

Madhivanan

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-31 : 05:25:16

there's a catch or i wouldn't complicate it that much

the thing is he wants to order desc on latest date for each username,
but group the dates on UserName.


xyz123	2004-12-28 00:00:00.000
xyz123	2004-12-15 00:00:00.000
xyz123	2004-12-01 00:00:00.000

abc123	2004-12-18 00:00:00.000
abc123	2004-12-05 00:00:00.000

def123	2004-12-09 00:00:00.000
def123	2004-12-08 00:00:00.000

at least that's how i understood it.

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-01-31 : 08:16:17

quote:
Originally posted by spirit1

there's a catch or i wouldn't complicate it that much
the thing is he wants to order desc on latest date for each username,
but group the dates on UserName.

xyz123 2004-12-28 00:00:00.000
xyz123 2004-12-15 00:00:00.000
xyz123 2004-12-01 00:00:00.000

abc123 2004-12-18 00:00:00.000
abc123 2004-12-05 00:00:00.000

def123 2004-12-09 00:00:00.000
def123 2004-12-08 00:00:00.000

at least that's how i understood it.

Go with the flow & have fun! Else fight the flow

If you are correct then here is one method

declare @t table (UserName varchar(50), DateSent datetime)
insert into @t
select 'xyz123' , '12/1/2004' union all
select 'xyz123', '12/28/2004' union all
select 'abc123', '12/5/2004' union all
select 'def123', '12/8/2004' union all
select 'abc123', '12/18/2004' union all
select 'xyz123', '12/15/2004' union all
select 'def123', '12/9/2004'

Select * into temp_T from (
Select * from @t group by UserName ,DAtesent
) t
go
alter table temp_t add Sno int identity
go
Select UserName,DateSent from temp_t order by Sno desc
drop table temp_t

The Result is

UserName DateSent
xyz123 2004-12-28 00:00:00.000
xyz123 2004-12-15 00:00:00.000
xyz123 2004-12-01 00:00:00.000
def123 2004-12-09 00:00:00.000
def123 2004-12-08 00:00:00.000
abc123 2004-12-18 00:00:00.000
abc123 2004-12-05 00:00:00.000

Madhivanan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-01-31 : 10:03:38

Will this do the job?


select
	Username ,
	DateSent = Max(DateSent)
from
	usertable
group by
	Username
order by
	Max(DateSent),
	Username

quote:
Originally posted by derketo

I know this probably has a simple solution...I just can't seem to figure it out.

I need to group some results together then order by a completely different field.

Here's the result set I need.

Username DateSent
abc123 12/1/2004
abc123 12/15/2004
xyz123 12/5/2004
xyz123 12/18/2004

Order by date then username grouped...anyone?

Thanks so much in advance.

Codo Ergo Sum

derketo
Starting Member

28 Posts

Posted - 2005-01-31 : 13:27:27

Thanks for all the help, guys. Michael, that will work. I'm doing unions though so looks like I'll have to work off a temp table. Thanks again, guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources