SQLite で検索結果の上位 10 件を取得する
SPECIAL
SQLite で上位 10 件を取得する
iPhone アプリのプログラミングでは、データベースとして SQLite を利用することが出来るようになっています。
この SQLite で "table1" テーブルの上位 10 件を取得したいような場合には、次のような SQL 文を実行することで得ることが可能です。
上位 10 件を取得する
SELECT DISTINCT table1.c1, table1.c2, table1.c3 FROM table1, table1 TMP WHERE table1.c3 >= TMP.c3 GROUP BY table1.c1, table1.c2, table.1c3 HAVING COUNT(*) <= 10 ORDER BY COUNT(*);
これにより、table1 に格納されている c1, c2, c3 を、c3 の先頭 10 番目までのレコードを昇順で取得することが可能です。
SQL 文としては、各行の c3 について、テーブル全体で自身以上の値を持つ行の個数を算出し、自身以上の値を持つ行が 10 個以内である行を見つけることで、昇順で最初に登場する 10 件を取得しています。
なお、Microsoft SQL Server 7.0 以上ならば "SELECT TOP 10 c1, c2, c3 FROM table1 ORDER BY c3" といった感じでできるところですが、SQLite の場合には、このようにして取得しないといけないようでした。
最新 10 件を残して残りを削除する
これを応用すれば、例えば table1 の c1 をキーとして、c2 にユリウス暦日が記録されていたときに、最新 10 件のデータを残してそれ以外のものは削除するといった場合は、次のような感じで実現できると思います。
話を簡単にするため、ここでは、今日の日付を '2010-07-09' というようにテキストで直接渡しています。
DELETE FROM table1 WHERE c1 IN (SELECT DISTINCT table1.c1 FROM table1, table1 TMP WHERE table1.c2 < TMP.c2 GROUP BY table1.c1 HAVING count(*) > 10);
こうすることで、各行のユリウス暦日 c2 を、テーブル全体で比較して、自身の行よりも小さい （過去の） ユリウス暦日を持つ行数を算出し、それが 10 個を上回るものを見つけることで、最近の 10 個を除いた過去の全ての c1 キー列を取得しています。
それを DELETE 文の削除対象となる c1 列として使用することで、最近の 10 件を残した、それ以外の全ての行を削除するということができていると思います。