Query 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query Help

Author

Topic

kj2398
Starting Member

2 Posts

Posted - 2005-09-20 : 15:22:11

Hopefully this will make sense:

I am trying to write a query to return the last event posted for the latest cycle (for which we have data) for each system.

I have 2 tables, cycle table and event table. For each cycle there are many events, cycle table contains multiple systems. Cycle and Event are linked by cycle_guid.

Any help is appreciated.

Sample Data

Cycle Table:
Cycle Guid, Cycle_Date , ID System
1, 9/20/05, 18
2, 9/20/05, 19
3, 9/20/05, 20

4, 9/19/05, 18
5, 9/19/05, 19
6, 9/19/05, 20

Event Table
Cycle GUID, Event ID, Event Time,
1, 146, Null
1, 147, Null
2, 146, 9/20/05 15:35
2, 147, 9/20/05 17:45
3, 146, 9/20/05 16:35
3, 147, Null
4, 146, 9/19/05 17:00
4, 147, 9/20/05 1:25
5, 146, 9/19/05 18:35
5, 147, 9/19/05 20:45
6, 146 9/19/05 15:00
6, 147 9/20/05 4:00

What I need returned is

System ID, Latest Cycle ID, Latest Cycle Date, Latest Event ID, Latest Event Time

18, 5, 9/19/05, 147, 9/19/05 20:45
19, 2, 9/20/05, 147, 9/20/05 17:45
20, 6, 9/20/05, 146, 9/20/05 16:45

I am currently doing a series of queries, one to get the last cycle date for each system, then one to get all the events for each system and then doing asp logic to loop thru the events and determine the latest event. While this does work, it is slow, and I think there should be a better way.
Thanks, kj

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-09-20 : 16:14:33

Select top 1 A.*
From Event
Where CycleId = (Select max(CycleId) From Cycle)
Order By EventTime desc

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

kj2398
Starting Member

2 Posts

Posted - 2005-09-20 : 16:48:40

cycle id is only included in this example because it is used to join the event and cycle table. cycle id is in reality a randomly generated 32 character guid.

so I don't see how doing a max on the cycle id is going to do any good here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources