Help needed for complex ordering issue

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 2008 Forums

Transact-SQL (2008)

Help needed for complex ordering issue

Author

Topic

mivey4
Yak Posting Veteran

66 Posts

Posted - 2012-08-10 : 06:37:30

I have records in a table that are in a sequential group type of order based on an eventId. I need to have the data re-ordered based on the eventId per group. As an example of what I have:

Example A.
EventId Date GroupA GroupB
1 2006-07-26 177942215 178002824
1 2006-07-26 177970314 178042020
1 2006-07-26 177971766 178044102
2 2007-02-26 177942215 178002824
2 2006-07-26 177971766 178044102
3 2007-04-23 177970314 178042020
3 2007-07-19 177971766 178044102
3 2007-03-14 177942215 178002824

As an Example of what I need:

Example B.
EventId Date GroupA GroupB
1 2006-07-26 177942215 178002824
2 2007-02-26 177942215 178002824
3 2007-03-14 177942215 178002824
1 2006-07-26 177970314 178042020
3 2007-04-23 177970314 178042020
1 2006-07-26 177971766 178044102
2 2006-07-26 177971766 178044102
3 2007-07-19 177971766 178044102

Example A is what I get when ordering the data on the EventId. Example B is the desired results but I can't seem to comprise a query that will get me that output.

I've tried using different partition configurations with the ROW_NUM function but still can't acquire the correct ordering.

I am open to any suggestions. Queries, ETL processing options or whatever. I just need the data ordered as in Example B

Thanks

Oracle OCA
Adaptec ACSP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-10 : 06:48:24

ORDER BY GroupB, EventID

N 56°04'39.26"
E 12°55'05.63"

mivey4
Yak Posting Veteran

66 Posts

Posted - 2012-08-10 : 07:06:05

Thanks SwePeso, your answer was so painfully simple that I feel like an idiot for overlooking it. So I must ask this question (which is likely to be just as simple)

Out of all the possible ways I could think of acquiring the answer, it hadn't occured for me to place the Group before the EventId in the ORDER BY Clause.

What was your rationale for that and what is the one major difference between the 2 statements:

ORDER BY EventId, GroupA, GroupB
ORDER BY GroupA, GroupB, EventId

Thanks!

Oracle OCA
Adaptec ACSP

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-10 : 07:50:59

I just looked at the expected resultset and noticed the sequences were in order for that permutation.

If GroupA and GroupB are correlated, there is no need to group by both columns.
For each order by column, the next column is sorting within the previous column and so on...

N 56°04'39.26"
E 12°55'05.63"

mivey4
Yak Posting Veteran

66 Posts

Posted - 2012-08-10 : 09:18:34

Thx

Oracle OCA
Adaptec ACSP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources