Problem sorting, grouping, and re-ordering a selection of records

Bottom line:

I have a Sub that should be re-ordering a group of records, but the query at the heart of it is not grouping and sorting the records as expected under rare, specific circumstances.

Background:

I'm developing an upgrade to a system for the education staff to post class information to our intranet. In the existing and in the upgraded system, the Classes_Dates table contains all the information related to the date, including a "Series" number.

The Series number was (and still is) used to group and sort dates, mostly to speed up the page generation on the front-end. Classes can have one or more (no limit) dates in a given series.

In the existing system, the series number is managed manually. Normally, it isn't an issue. Classes are entered in sequentially, in the order they occur. Occasionally, a class gets added in the middle of the chronological flow, and the staff will manually re-order the series numbers to properly group/sort the dates. It works, but is difficult for new staff to learn and existing staff to retain if they do not frequently use the system.

In the upgrade, I wrote a sub to automatically handle the re-ordering of the groups. I'm trying to keep the concept, but bury it so the staff don't need to be aware it still exists.

Here's the sub itself, called every time a new class date is added:

Sub ReorderGroups(intClassID)
    strSQL = "SELECT DateID, Series, ClassStart "
    strSQL = strSQL & "FROM Classes_Dates "
    strSQL = strSQL & "WHERE ClassID = " & intClassID & " "
    strSQL = strSQL & "GROUP BY Series, ClassStart, DateID "
    strSQL = strSQL & "ORDER BY ClassStart;"

    Dim objSQLDB : Set objSQLDB = CreateObject("ADODB.Command")
    objSQLDB.ActiveConnection = strSQLConn

    Dim objDates : Set objDates = Server.CreateObject("ADODB.Recordset")
    objDates.Open strSQL, strSQLConn, adOpenDynamic, adLockReadOnly, adCmdText
    If Not objDates.BOF Then objDates.MoveFirst
    If Not objDates.EOF Then
        Dim intNewSeries : intNewSeries = 1
        Dim intCurrentOld : intCurrentOld = cLng(objDates("Series"))

        Do Until objDates.EOF
            If intCurrentOld <> cLng(objDates("Series")) Then
                intNewSeries = cLng(intNewSeries) + 1
                intCurrentOld = cLng(objDates("Series"))
            End If

            objSQLDB.CommandText = "UPDATE Classes_Dates SET Series = " & intNewSeries 开发者_开发百科& " WHERE DateID = " & objDates("DateID")
            objSQLDB.Execute ,,adCmdText

            objDates.MoveNext
        Loop
    End If
    objDates.Close
    Set objDates = Nothing
    Set objSQLDB = Nothing
End Sub

I'm sure there's a more efficient way to write this, but my first concern was getting it working - then I may post it over to CodeReview.SE for some help with optimization.

The sub works great as long as there are not two series with overlapping dates. The following:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
GROUP BY Series, ClassStart, DateID
ORDER BY ClassStart;

Is gathering this result set:

DateID  Series  ClassStart
------  ------  --------------
49  1   20100907080000
51  1   20100913080000
50  1   20100916080000
56  2   20100921080000
57  2   20100927080000
58  2   20100929080000
'-- snip --'
670 12  20110614080000
671 12  20110615080000
672 13  20110705080000
676 15  20110707080000
674 14  20110709090000
673 13  20110714080000
675 14  20110716080000

Instead of what I expected:

DateID  Series  ClassStart
------  ------  --------------
49  1   20100907080000
51  1   20100913080000
50  1   20100916080000
56  2   20100921080000
57  2   20100927080000
58  2   20100929080000
'-- snip --'
670 12  20110614080000
671 12  20110615080000
672 13  20110705080000
673 13  20110714080000
676 15  20110707080000
674 14  20110709090000
675 14  20110716080000

What do I need to fix in the SQL? Or is there a better way to get the same end result?

The latter would likely be better as I can see now that I look at it again this is not going to scale well as time goes on...

I think you want:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
GROUP BY Series, ClassStart, DateID
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , ClassStart

Note that if the (Series, ClassStart, DateID) is a unique key in this table, then you don't even need the GROUP BY:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , ClassStart

And just to catch the (probably rare) case where two Series have the same MIN(ClassStart), you should use this one so data from these two Series don't get mixed up in the results:

SELECT DateID, Series, ClassStart
FROM Classes_Dates
WHERE ClassID = 11
ORDER BY MIN(ClassStart) OVER(PARTITION BY Series) 
       , Series
       , ClassStart

How the query works:

What your problem describes is that you want the data shown in groups (of same Series). But you also want these groups ordered depending on the MIN(ClassStart) of every group.

To find MIN(ClassStart) we'd have to use GROUP BY Series but we can't do that because then the muptiple rows (of same group) would collapse into one.

This is what MIN(ClassStart) OVER(PARTITION BY Series) achieves. It calculates the minimum of ClassStart as if we had used GROUP BY Series.