Geek Answers Handbook

Sort, group, and reorder records sort

Bottom line:

I have a Sub that needs to reorder a group of records, but it is not based on grouping and sorting records, as expected, in rare special circumstances.

Background:

I am developing an upgrade to a system where educators can send class information to our intranet. In the existing and updated system, the Classes_Dates table contains all the information related to the date, including the "Series" number.

The series number was (and still is) used to group and sort dates, mainly to speed up the generation of pages on the interface. Classes can have one or more (without limitation) dates in a given series.

In an existing system, the batch number is manually controlled. This is usually not a problem. Classes are entered sequentially, in the order they occur. Sometimes a class is added in the middle of the chronological stream, and staff manually reorders series numbers to correctly group / sort dates. It works, but it’s hard for new employees to learn, and existing staff must maintain it if they don’t use the system often.

In the update, I wrote sub to automatically handle group reordering. I try to preserve the concept, but bury it, so staff do not need to know that it still exists.

Here is 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 is a more efficient way to write this, but my first problem was getting it to work - then I can send it to CodeReview.SE for some help with optimization.

Sub works fine if there are no two episodes with overlapping dates. Following:

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

Collects 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 was expecting:

  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 SQL? Or is there a better way to get the same end result?

The latter will probably be better, as I can see now, when I look at it again, it will not scale well over time ...

+4
source share
1 answer

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 (Series, ClassStart, DateID) is a unique key in this table, you don't even need 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 (possibly rare) case when two series have the same MIN (ClassStart), you should use it so that data from these two series do not mix 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 request works:

Your problem describes that you need data displayed in groups (from the same Series ). But you also want these groups ordered according to the MIN(ClassStart) each group.

To find MIN(ClassStart) , we will need to use GROUP BY Series , but we cannot do this, because then the lines of the muptiple (of the same group) would collapse by one.

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

+2
source

More articles:


All Articles