# HG changeset patch
# User HIROSE Yuuji <yuuji@gentei.org>
# Date 1468801573 -32399
# Node ID 3ae8b70b8395c9e741e088ace3f7c63d4ac793c2
# Parent  8a0cf46afb0a33d2f2728f0230a9deb9765cbda0
Add commentary about SQL

diff -r 8a0cf46afb0a -r 3ae8b70b8395 s4-funcs.sh
--- a/s4-funcs.sh	Mon Jul 18 00:32:46 2016 +0859
+++ b/s4-funcs.sh	Mon Jul 18 09:26:12 2016 +0859
@@ -1511,6 +1511,22 @@
       GROUP BY blogid ORDER BY val DESC LIMIT 50
      ) m
      ON b.id=m.blogid;"
+    # This should be as follows
+    : <<EOF
+WITH arts AS(
+  SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid,
+         a.blogid, a.id id, s.val ctime
+  FROM article a NATURAL JOIN article_s s
+  WHERE s.key = 'ctime' AND a.author='$user'
+  GROUP by s.id
+)
+SELECT a0.brid,a0.blogid,a0.id,a0.ctime
+FROM arts a0
+     JOIN
+     (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1
+     ON a0.blogid=a1.blogid AND a0.ctime=a1.mct
+ORDER BY ctime DESC LIMIT 50;
+EOF
 
     cat<<-EOF
 	<div class="fold">