all groups > sql server programming > march 2004 >
You're in the

sql server programming

group:

Help with query (sorting and grouping fields)


Help with query (sorting and grouping fields) Star
3/8/2004 9:26:04 PM
sql server programming:
Hi,

I have table like this

CODE Name DateFrom
1 d american 2004/03/06 15:41:28
1 c american 2004/03/06 15:39:23
1 b american 2004/03/06 15:39:23

2 burt walker 2004/03/01 12:46:20

3 x john 2003/01/01 15:10:30
3 z john 2004/01/01 10:10:20


I want to make a query that returns this information grouped by code and
sorted by name and datefrom (desc)
I mean, each group (grouped by code) has to be sorted by datefrom (desc) and
name (asc), but the final query will give me everything sorted by name
(using the first of each group to find out which one goes first)

This should be the result:

2 burt walker 2004/03/01 12:46:20

1 d american 2004/03/06 15:41:28
1 b american 2004/03/06 15:39:23
1 c american 2004/03/06 15:39:23

3 z john 2004/01/01 10:10:20
3 x john 2003/01/01 15:10:30

I tried this:

select * from Table order by code, datefrom desc, name

and it sorts each individual subgroup, but it also sorts by code...

Sorry If I didn't explain it too clearly. I hope you guys can understand the
problem and give me some hints.

Thanks!

Re: Help with query (sorting and grouping fields) Star
3/9/2004 10:29:11 AM
Thanks for your answer, Vishal.

Here is another example

2 s 2004/03/01 12:46:20
3 a 2003/01/01 15:10:30
1 j 2004/03/06 15:39:23
3 u 2004/01/01 10:10:20
1 g 2004/03/06 15:41:28
1 a 2004/03/06 15:39:23



First, we group by codes:

1 j 2004/03/06 15:39:23
1 a 2004/03/06 15:39:23
1 g 2004/03/06 15:41:28

3 a 2003/01/01 15:10:30
3 u 2004/01/01 10:10:20

2 s 2004/03/01 12:46:20

Now, for each group, we sort by Date

1 g 2004/03/06 15:41:28
1 j 2004/03/06 15:39:23
1 a 2004/03/06 15:39:23

3 u 2004/01/01 10:10:20
3 a 2003/01/01 15:10:30

2 s 2004/03/01 12:46:20

Now, for each group with the same date, we sort by name

1 g 2004/03/06 15:41:28
1 a 2004/03/06 15:39:23
1 j 2004/03/06 15:39:23

3 u 2004/01/01 10:10:20
3 a 2003/01/01 15:10:30

2 s 2004/03/01 12:46:20

And now, we sort the group by name, using the first record of each group
(The order is g,s,u)

1 g 2004/03/06 15:41:28
1 a 2004/03/06 15:39:23
1 j 2004/03/06 15:39:23

2 s 2004/03/01 12:46:20

3 u 2004/01/01 10:10:20
3 a 2003/01/01 15:10:30



That's should be the result of the query. Maybe it's not as simple as I
thought at the beginning.
I hope somebody can help me. Thanks!



Re: Help with query (sorting and grouping fields) Vishal Parkar
3/9/2004 10:38:52 AM
hi star,

[quoted text, click to view]
(using the first of each group to find out which one goes first)<<

can you elaborate your above statement more. preferably with the help of some relevent sample
records and expected result set.


--
Vishal Parkar
vgparkar@yahoo.co.in

Re: Help with query (sorting and grouping fields) oj
3/9/2004 10:41:36 AM
select *
from tb
order by code asc, datefrom desc, name asc

--
-oj
http://www.rac4sql.net


[quoted text, click to view]

Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 1:59:41 PM
Now toss in the tricky part. ;-)

select
code,
name,
datefrom
from tb tboo
order by
(
select top 1 name
from tb tbii
where tbii.code = tboo.code
and tbii.datefrom = tbii.datefrom -- Consider ignoring the time.
order by
code asc, -- Not really needed.
datefrom desc, -- Not really needed.
name asc -- Is really needed.
) asc,
code asc,
datefrom desc,
name asc

Bye,
Delbert Glass

[quoted text, click to view]

Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 2:14:03 PM
Opps, change this
and tbii.datefrom = tbii.datefrom -- Consider ignoring the time.
to this
and tbii.datefrom = tboo.datefrom -- Consider ignoring the time.


Also you do want to ignore the time,
so further change it to this:

and cast(cast(tbii.datefrom as integer) as datetime)
= cast(cast(tboo.datefrom as integer) as datetime)

Also you'll want to ignore the time in the ORDER BY clauses.

cast(cast(tbii.datefrom as integer) as datetime) desc,


Of course use whatever you favorite expression is for
ignoring the time.

If you need it to run faster,
create a calculated column of the date with the time ignored
and use the calculated column in multi-column index
(code asc, datefromWOtime desc, name asc)
..

Bye,
Delbert Glass

[quoted text, click to view]

Re: Help with query (sorting and grouping fields) oj
3/9/2004 3:03:37 PM
Hi,

I must not have understood your requirement. ;(
/*
And now, we sort the group by name, using the first record of each group
(The order is g,s,u)

1 g 2004/03/06 15:41:28
1 a 2004/03/06 15:39:23
1 j 2004/03/06 15:39:23

2 s 2004/03/01 12:46:20

3 u 2004/01/01 10:10:20
3 a 2003/01/01 15:10:30
*/

create table #tmp(Code int,Name sysname,DateFrom datetime)
insert #tmp select 2, 's', '2004/03/01
12:46:20'
union all select 3, 'a', '2003/01/01 15:10:30'
union all select 1, 'j', '2004/03/06 15:39:23'
union all select 3, 'u', '2004/01/01 10:10:20'
union all select 1, 'g', '2004/03/06 15:41:28'
union all select 1, 'a', '2004/03/06 15:39:23'
go
select *
from #tmp
order by Code asc, DateFrom desc, Name asc

select
code,
name,
datefrom
from #tmp tboo
order by
(
select top 1 name
from #tmp tbii
where tbii.code = tboo.code
and tbii.datefrom = tboo.datefrom -- Consider ignoring the time.
order by
code asc, -- Not really needed.
datefrom desc, -- Not really needed.
name asc -- Is really needed.
) asc,
code asc,
datefrom desc,
name

select CODE, Name, DateFrom
from (
select CODE, Name, DateFrom, (
select top 1 Name from #tmp T2
where T2.CODE = T1.CODE
order by DateFrom desc) TopNameForThisCode
from #tmp T1
) T
order by TopNameForThisCode, CODE, DateFrom desc
go

drop table #tmp
go


--
-oj
http://www.rac4sql.net


[quoted text, click to view]

Re: Help with query (sorting and grouping fields) oj
3/9/2004 3:50:08 PM
It might even be simpler (without all this subquery) if the OP posts
ddl+sample data+expected result.

--
-oj
http://www.rac4sql.net


[quoted text, click to view]

Re: Help with query (sorting and grouping fields) Star
3/9/2004 4:26:50 PM
[quoted text, click to view]

Thanks for your answer, oj, but that doesn't work. I don't get the results
sorted by name for each group.




Re: Help with query (sorting and grouping fields) Star
3/9/2004 4:41:46 PM
Delbert,

Thanks a lot for your help. It worked *almost* great.
For some reason, I still get a name unsorted. I've tried to find where is
the problem, but I cannot see it.
Here is a screenshot of the results of my query (as you can see, there is
one record unsorted)

http://lemforever.com/temp/query.jpg

Thanks!

Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 4:53:52 PM
Looks like you expect 59760 and 59744 to have been adjacent.

I suspect the code column is not integer
and 58760's code is lower case leter L;
rather than, the letter one.

Bye,
Delbert Glass

[quoted text, click to view]

Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 5:34:06 PM
Relocate the subquery to the select list
giving it an alias, and use the alias
in the ORDER BY. That way you can see the
value. Which might be helpfull to figure
out what is going on. [1]

Below is an example of the rearrangement
(but does not contain the ignore the time part stuff).

[1] Perhaps, the ORDER BY clause in your subquery
was incomplete causing the NameOfGroupLeader
to be undetermined. If you still have problems,
post your lastest query and the output
(including the NameOfGroupLeader column).

select
(
select top 1 name
from tb tbii
where tbii.code = tboo.code
and tbii.datefrom = tboo.datefrom -- Consider ignoring the time.
order by
code asc, -- Not really needed.
datefrom desc, -- Not really needed.
name asc -- Is really needed.
) as NameOfGroupLeader,
code,
name,
datefrom
from tb tboo
order by
NameOfGroupLeader asc,
code asc,
datefrom desc,
name asc

Bye,
Delbert Glass

Re: Help with query (sorting and grouping fields) Steve Kass
3/9/2004 5:37:25 PM
Star,

How about

select CODE, Name, DateFrom
from (
select CODE, Name, DateFrom, (
select top 1 Name from yourTable T2
where T2.CODE = T1.CODE
order by DateFrom desc) TopNameForThisCode
from yourTable T1
) T
order by TopNameForThisCode, CODE, DateFrom desc

SK

[quoted text, click to view]
Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 5:54:53 PM

(in another branch of the thread)
[quoted text, click to view]

Surely, you've notice I've been doing:
TopNameForThisCodeOnThisDay

Include/exclude the date condition in the subquery
as desired.

(be sure to say which way you want in your next post)

Bye,
Delbert Glass

Re: Help with query (sorting and grouping fields) Star
3/9/2004 6:00:24 PM
[quoted text, click to view]

Yes, exactly.

[quoted text, click to view]

The type of Code is integer. I thought the same than you the first time, but
it's an integer. Here is the definition:

CREATE TABLE [Subs_Subjects] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [int] NOT NULL ,
[Name] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateFrom] [datetime] NOT NULL CONSTRAINT [DF_Subs_Subjects_DateFrom]
DEFAULT (getdate()),
[AKA] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ethic] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Group] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TitleInGroup] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DOB] [datetime] NULL ,
[Race] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Sex] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Height] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Weight] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Eyes] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hair] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tattoos] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Occupation] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLNo] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DLState] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AlienRegistration] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[C0] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C5] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C6] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C7] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C8] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[C9] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FK_CustomGroup_ID] [int] NULL ,
[FK_CustomSubGroup_ID] [int] NULL ,
[FK_Pictures_ID] [int] NULL ,
[ImportName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ImportDate] [datetime] NULL ,
[Source] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_HumIntel] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]
GO


Thanks a lot for your help, Delbert.

Re: Help with query (sorting and grouping fields) Star
3/9/2004 6:06:06 PM
Thanks for your answer, Steve.

It looks fine, but there is problem where we have the same date. Here is a
screenshot:

http://lemforever.com/temp/query2.jpg


Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 6:08:01 PM

I said:

[quoted text, click to view]

don't do that, it rounds everything after lunch to the following day.

----

However that is not want causing 59760 and 59744 to be non-adjacent.

The subquery needs to include name in the ORDER BY clause for TOP
(thus the ORDER BY clause needs to be present and non-commented out).

Bye,
Delbert Glass

Re: Help with query (sorting and grouping fields) Steve Kass
3/9/2004 6:32:44 PM
Just add [name] at the end of the order by list, if you want ascending
name for maching code and date.

SK

[quoted text, click to view]
Re: Help with query (sorting and grouping fields) Star
3/9/2004 6:50:06 PM
Yes! It works perfect now. Thanks a lot, Steve.

Re: Help with query (sorting and grouping fields) Star
3/9/2004 7:12:51 PM
Here it is. For some reason, it seems that belongs to a different group.

http://lemforever.com/temp/query3.jpg

But it's ok, Delbert. I think I can use Steve's solution, which is as fast
as yours.
If you think it's too much trouble to find the problem, we can use the ofher
solution.

Thanks a lot. I really appreciate your help.

Re: Help with query (sorting and grouping fields) Delbert Glass
3/9/2004 7:13:38 PM
Here is the trouble:
The time part in the date condition of the subquery
is not being ignored.

The rows 59815 and 59761 come where they did
because they got 59762's name since they have
exactly the date&time as it does (and same code).
Meanwhile, row 59760 got it's own name
since there is nothing else with a code of 1
and exactly the same date&time;
and thus, come out where it did.

Bye,
Delbert Glass

[quoted text, click to view]

Re: Help with query (sorting and grouping fields) Star
3/10/2004 9:44:56 AM
Thanks for the clarification, Delbert.
If I find out something, I'll let you know.

Thanks again

Got something to say? Click here to post a reply to this thread.
Don't see what you're looking for? Try a search.
View other messages in the sql server programming group.
AddThis Social Bookmark Button
View Other Months
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
home · blog · groups Questions? Comments? Contact the dn