I'd rather you replied than used a hidden update like that. Almost missed this one. :)
Anyway, tracking such things take just a little effort, but can nonetheless be done easily. Here it is with Firefox, Notepad++, and Oracle.
- Copy the table from Saints in our Book
- Regex replace ^(\S+)(\s)([^\t]+)(\s)(\S+)(\s)(\S+\s\S+)(\s\S+).+ with \t SELECT \1,\2'\3',\4\5,\6'\7',\8 FROM Dual UNION ALL
- Replace None with 0
- Dress up the data in a CTE
- Add other CTEs to help
Now we have:
Now for some useless data:
SELECT
(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP, Writeups HAVING
+Count(*) > 1) Both,
(SELECT COUNT(*) FROM Close) Close,
(SELECT COUNT(COUNT(*)) FROM Data GROUP BY XP HAVING Count(*) > 1)
+ XP,
XP_Diff_Min,
XP_Diff_Max,
XP_Diff_Avg,
(SELECT COUNT(COUNT(*)) FROM Data GROUP BY Writeups HAVING Count(*
+) > 1) Writeups,
Writeups_Diff_Min,
Writeups_Diff_Max,
Writeups_Diff_Avg
FROM
MMA;
Results in (2015/07/10, 2015/07/20, 2015/07/29, 2015/09/02, 2016/01/06, 2016/03/09, 2016/05/03, 2016/06/01, 2016/7/22):
BOTH CLOSE XP XP_DIFF_MIN XP_DIFF_MAX XP_DIFF_AVG
+ WRITEUPS WRITEUPS_DIFF_MIN WRITEUPS_DIFF_MAX WRITEUPS_DIFF_AVG
---------- ---------- ---------- ----------- ----------- ----------- -
+--------- ----------------- ----------------- -----------------
0 7 31 0 851484 1468.48538
+ 135 -56940 56902 .545321637
1 5 34 0 851046 1466.34161
+ 132 -56940 56902 .544525547
1 6 30 0 850755 1466.34161
+ 132 -56940 56902 .544525547
0 4 30 0 849885 1462.07569
+ 128 -56761 56940 .54294032
0 10 27 0 847648 1453.62373
+ 135 -56761 56940 .781476122
0 11 37 0 847042 1453.62952
+ 127 -56761 56940 .781476122
0 5 30 0 845891 1451.52457
+ 135 -56761 56940 .780346821
1 7 30 0 845449 1449.4329
+ 127 -56761 56940 .779220779
1 8 33 0 844286 1449.44733
+ 130 -56761 56940 .779220779
To see the actual listings of those that are close:
SELECT
Data.Ranking,
Data.Name,
Data.XP,
Data.Title,
Data.Writeups
FROM
Data,
Close
WHERE
Data.Ranking BETWEEN Close.Ranking AND Close.Ranking + 1
ORDER BY
Data.Ranking;
RANKING NAME XP TITLE WRITEUPS
---------- -------------------- ---------- --------------- ----------
37 eyepopslikeamosquito 29895 Canon (20) 1289
38 planetscape 29894 Canon (20) 1289
412 ChemBoy 4651 Priest (14) 282
413 danger 4645 Priest (14) 280
426 fisher 4535 Priest (14) 152
427 Theo 4529 Priest (14) 154
510 OeufMayo 3800 Curate (13) 146
511 lin0 3799 Curate (13) 149
567 logan 3522 Curate (13) 169
568 mandog 3522 Curate (13) 167
642 jaredor 3187 Curate (13) 42
643 Daruma 3186 Curate (13) 41
664 csuhockey3 3086 Curate (13) 122
665 giulienk 3085 Curate (13) 122
14 rows selected.
Obviously, you can change the definition of "close" in the CTE.
As long as the database supports analytical functions, this ought to work outside Oracle. But, you'll llikely have to remove " FROM Dual" or add a Dual CTE, like: Dual AS (SELECT Null).
Update 2014/07/20: Transposed backwards XP and Writeups! Some of the results are different. |