Sort/Group need help - 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

General SQL Server Forums

New to SQL Server Programming

Sort/Group need help

Author

Topic

NTC
Starting Member

21 Posts

Posted - 2007-11-07 : 21:30:23

need some sql assistance....have this:
Model Date
A 2/2/07
B 1/2/07
C 1/1/07
A 3/1/07
C 2/2/07

I think you get the idea....random.... I need sorted by Model that's important - but then with lowest date first:

C 1/1/07
C 2/2/07
B 1/2/07
A 2/2/07
A 3/1/07

have the ability to create a control column if that helps...but am struggling with the sql statement that will give me the correct results and would welcome input....thanks in advance...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 21:36:45

[code]
Select Model,Date
FROM [Table]
Group by Model,Date
Order by Model desc, Date asc
[/code]

NTC
Starting Member

21 Posts

Posted - 2007-11-07 : 21:49:20

thnks but doesn't result in the correct sort/order...

has models with more recent dates appearing before models with older
Model Date
Pool 7/2/2007
Pool 7/3/2007
B99 4/27/2005
B99 5/5/2005
B99 5/6/2005
B99 5/9/2005
B98 4/25/2005
B98 4/26/2005
B98 4/27/2005

just a snippet from the results...the B98 has the earliest date 4/25 and so should be the first model group

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 21:55:01

What is the proposed sort order for "Model" then? If you just order by date, the models will be out of order. If you sort by model as well, then it will be essentially in alphabetical order either asc or desc depending on what you want.

If you want the earliest date first, you have switch the order by to:

ORDER BY Date asc , Model desc

my original response would produce your original sample of desired results.
It helps to be a little more specific so that you get what you need.

NTC
Starting Member

21 Posts

Posted - 2007-11-07 : 22:04:45

well data speaks louder than words....the original example of what is needed is valid;

the model with the lowest date first; grouped with that entire model's group
the next model with the next lowest date next; grouped with that entire models group
etc.

I'm thinking maybe it needs a control column created:
lowest date/model = 1 then all of that model = 1
next lowest date/different model = 2 then all of that model = 2
etc.
and then sort by the control column.... but this could be a red herring idea....
There is a logical pattern so it should be do-able...just can't get it together to do however.....

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 22:42:20

That is clearer, and yes ..easy with another column to identify sort order

quote:

If exists (Select * from sysobjects where name = 'Foo')
Drop Table Foo
go
Create Table Foo (Model char(5) not null,TestDate datetime not null,sortorder int null)
go

Insert Into Foo (Model,TestDate)
Select 'Pool', '7/2/2007' UNION ALL
Select 'Pool','7/3/2007' UNION ALL
Select 'B99', '4/27/2005' UNION ALL
Select 'B99', '5/5/2005' UNION ALL
Select 'B99', '5/6/2005' UNION ALL
Select 'B99', '5/9/2005' UNION ALL
Select 'B98', '4/25/2005' UNION ALL
Select 'B98', '4/26/2005' UNION ALL
Select 'B98', '4/27/2005'

GO

Select model,min(testdate) as mindate,identity(int,1,1) as oid
Into #Rank
FROM Foo
GRoup by model

Update FOo
Set Sortorder = oid
FROM #Rank inner join Foo on #Rank.Model = Foo.Model

Select Model,TestDate
FROM Foo
Order by SortOrder asc,TestDate asc

Drop Table #Rank

Produces:

quote:

B98 2005-04-25 00:00:00.000
B98 2005-04-26 00:00:00.000
B98 2005-04-27 00:00:00.000
B99 2005-04-27 00:00:00.000
B99 2005-05-05 00:00:00.000
B99 2005-05-06 00:00:00.000
B99 2005-05-09 00:00:00.000
Pool 2007-07-02 00:00:00.000
Pool 2007-07-03 00:00:00.000

So if you follow that...it is essentially what you proposed above.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-11-08 : 08:50:06

Just join your model table to a result set that returns the lowest date per model.


select 
  m.Model, m.[Date]
from 
  models m
inner join  
  (
   select Model, Min(date) as MinDate 
   from models 
   group by model
  ) m2 on m.Model = m2.Model
order by 
  m2.MinDate ASC, m.Model ASC, m.[Date] ASC

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-08 : 09:53:01

Or this one, if you have SQL Server 2005

SELECT		Model,
		TestDate
FROM		Model
ORDER BY	MIN(TestDate) OVER (PARTITION BY Model),
		Model,
		TestDate

E 12°55'05.25"
N 56°04'39.16"

NTC
Starting Member

21 Posts

Posted - 2007-11-08 : 10:54:30

Really great - thank you all. Don't have SqlS5 - so copied in jeff's inner join code, only since it was less than yak's when I looked at both this a.m., and it worked like a champ out of the gates. Much appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources