Currently I have 3 values that I need to sort, in the following order: gameDate, some-column-that-guarantees-insertion-order, team score.
Obviously, with the second sort param as a unique key, the 3rd sort param will be ignored. So, rather than sorting by, say, game result PK id, in the production app I created a "sort" column that stores a non-unique incremented value (see query result below), which then allows a
SORT BY gameDate, sort, total DESC
where sorting by total gives me the winning (or tie-ing) team first, so I don't have to
if(teamA.total >= teamB.total) // show winner on left, loser on right
in the application layer to generate the weekly games report.
So, in order to have my sorting cake, and not maintain an intermediary sort column, it appears that a datetime or timestamp could do the trick; however, the catch is that I need the sort on the insertion date non-uniquely (date value must be the same for both participant team result rows), otherwise, it's the same as sorting on game result PK id, the 3rd sort column will be ignored.
Why do I need to do this? Requirements are that the most recent game results should be displayed last on any given game date (which sort by gameDate, PK id does), and my own requirement is that the 3rd column sort on total is reachable (which again, is NOT possible with a unique-key-column as the 2nd sort param).
Probably even now this is clear as mud ;-)
ORIGINAL
I have a list of game results that need to be displayed in a report, sorted by game date, followed by a fixed sort order (i.e. cannot sort on game ID as the most recently displayed games must display last for each date in the report) and finally by winning team score.
In the current version of the app I'm using an int sort column, which, on game result submission, is queried for and then incremented by 1 for the db insert operation. The end result is that I can run a query like:
SELECT id, gameID, gameDate, teamID, total, sort
FROM games
ORDER BY gameDate, sort, total desc
+----+--------+------------+--------+-------+------+
| id | gameID | gameDate | teamID | total | sort |
+----+--------+------------+--------+-------+------+
| 1 | 3 | 2011-12-01 | 798 | 6 | 161 |
| 2 | 3 | 2011-12-01 | 106 | 3 | 161 |
| 3 | 4 | 2011-12-01 | 78 | 8 | 162 |
| 4 | 4 | 2011-12-01 | 106 | 7 | 162 |
| 5 | 5 | 2011-12-04 | 106 | 4 | 163 |
| 6 | 5 | 2011-12-04 | 167 | 3 | 163 |
+----+--------+------------+--------+-------+------+
However, I'd prefer to not deal with maintaining a sort column. Yes, I can sort by gameDate and then by game result id PK in the query, and determine winning team sort in the application layer, doing an if(teamA.total >= teamB.total) ...., but that is ugly as well ;-)
I'm thinking maybe a DateTime, or Timestamp (if that allows non-unique) might do the trick. Bit risky given that MySQL does not, as I recall, handle microseconds, so the problem of 2 different game results being submitted at the same time is a possiblity (which could cause the game report to display incorrectly).
Anyway, bottomline is that I need to find a way to sort by game date and preserve insertion order so I can get the last sort clause on team's score for the game (i.e. sort by winning team).
Ideas appreciated
Thanks
storing insertion as a DateTime seems the easier and more maintainable approach (vs. hacked sort column currently in place); the sort clause then becomes:
SORT BY gameDate, createDate, gameID, total DESC
Where createDate is non-unique, which means that subsequent sort columns are reachable. In this scenario even if 2 separate game results are submitted at the same time, it won't matter as gameID sort provides the gateway to the desired final sort, the winning team ;-)
So basically I can do in the application layer:
games.map(x=> dao.insert(x))
where each participant team in the game result has the same DateTime value for createDate column.
Should do the trick...