Sorting data - 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)

Sorting data

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2003-02-18 : 10:38:07

This may seem trivial to some. But hopefully there is some who can help. I have a production reporting system setup in SQL2000. I have the following script running:

SELECT DISTINCT caliper As Caliper,
MIN (CaptureDateTime) AS StartTime, MAX (CaptureDateTime) AS EndTime,
DateDiff (mi, MIN (CaptureDateTime), MAX (CaptureDateTime)) AS Caliper_Total_Time

FROM tbl_3PMProduction

WHERE
(tbl_3PMProduction.product = 3400) AND
(tbl_3PMProduction.campaign_number = 301526)

GROUP BY caliper

bla, bla, bla

The results are somnething like this:

.015 2003-02-17 14:40:43.570
2003-02-18 02:41:42.910
721
.022 2003-02-17 17:36:42.800
2003-02-17 23:28:43.580
352
.028 2003-02-18 02:42:37.910
2003-02-18 07:03:41.590
261
.031 2003-02-17 17:35:42.780
2003-02-17 17:35:42.780
0
.036 2003-02-18 07:04:41.610
2003-02-18 10:17:35.640
193

The problem? How do I sort so that my dates are in order while still maintaining the same information? I tried seting the CaptureDateTime field in the Group by clause. When I did this I got every record instead of the min and max. There are some 3000 records being viewed by this query. I am looking only for the min and max time, by caliper, in order of time....caliper does not need to be in order.

Help?

Thanks.
John

lozitskiy
Starting Member

28 Posts

Posted - 2003-02-18 : 11:39:35

Will it help?

create table #test(id int,idate datetime)

insert #test
select 1,getdate()-35
insert #test
select 1,getdate()-25
insert #test
select 2,getdate()-45
insert #test
select 2,getdate()-15

select id,min(idate),max(idate)
from #test
group by id
order by min(idate)

-------------
MCP MSSQL

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-18 : 12:05:56

Add:

ORDER BY MIN(CaptureDateTime)

to the end of your query.

Edit: just realized I basically repeated the answer from the last post -- sorry !!! (you were much more subtle than I)

- Jeff

Edited by - jsmith8858 on 02/18/2003 12:26:32

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources