降順で並び替えて昇順で受け取りたい

Question

sqlite3 を使用しています。

名前 | 成績
A    |  80
B    |  65
C    |  90
D    |  77
E    |  46
F    |  30

例えば上記のようなテーブルがあったとします。
成績がいい人3人のレコードを昇順で取得したいのですが、どのようにすればいいか困っています。

select * from テーブル order by 成績 desc limit 3;

だと上位3人を抽出できるのですが、いい人の順番

C  |  90
A  |  80
D  |  77

となってしまいますよね。
そうではなく、

D    |  77
A    |  80
C    |  90

という結果が欲しいのです。

よろしくおねがいします。

chukenkenkou · Accepted Answer

「TOP n」は、SQL Serverの独自機能であり、他の主要なRDBMSでは使えません。
また、分析関数も、Oracle、SQL Server 2005などのいくつかのRDBMSでしか実装されていません。

そのため、自前で行に通番を振る必要があります。

＜成績上位から行に通番を付ける＞
select
  (select count(*)+1 from tbl1 where 成績>x.成績) as rownum,*
 from tbl1 as x;

上記のクエリで通番を付けられますから、このクエリの結果から上位3位タイまでを得るクエリを作ります。

＜期待の結果を得るSQL＞
select 名前,成績
 from(select
       (select count(*)+1 from tbl1 where 成績>x.成績) as rownum,*
         from tbl1 as x) as y
 where rownum<=3
 order by 成績
;

fuuten_no_neko · Answer

＃６です。
>『) AS 式1, s.成績』　の　『式１』ってなんですか？
MS-ACCESSが「勝手に」書き加えた「 AS 式1」の一部です。SQLビューで書いたSQL文をデザインビューで見るとこうなります。消せば良かったのかもしれませんが、手抜きしました。

chukenkenkou · Answer

>『) AS 式1, s.成績』　の　『式１』ってなんですか？

#6さんへの質問ですが、ついでなので回答しておきます。

#6さんのSQLを、もう少し簡単に書くと、以下のようなSQLになります。

SELECT 名前,s.成績
 FROM tbl1 AS s
 WHERE (SELECT Count(*)+1 FROM tbl1 where 成績>s.成績)<=3
 ORDER BY s.成績;

「as 式1」のところは、「名前」列を指定すればいいところを、誤記されたのだと思います。

なお、やっていることは、#6さんも私も同じで、テーブルを自己結合し、成績の大きい件数をカウントし、+1することで、行に通番を振っています。

A | 80→80より大きい行数は1件
B | 65→同様に3件
C | 90→同様に0件
D | 77→同様に2件
E | 46→同様に4件
F | 30→同様に5件

それぞれ件数に+1することで、大きい順に1～6の通番を付けることができます。
その通番が3以下のもの（上位3位タイまで）を最終的な検索結果として得ています。

chukenkenkou · Answer

>最後の　『order by 成績』　の部分は必要なのでしょうか？

ほぼすべてのRDBMSは、「ORDER BY」指定がないと、検索結果の返却順序は保証していません。

「保証していないと言っているが、実はプライマリキー順や格納順になるのでは？」と考える人もいるようですが、例えば格納と削除を繰り返したり、プライマリキーの逆順やランダムな格納をすると、すぐに検索結果の順序が乱れたりします。

ジョイン、group byなどを行う場合、内部的にソートされることもありますが、その結果はRDBMS側としては保証されていませんから、内部処理が変わって順序が不定になっても、誰にも文句を言えません。

chukenkenkou · Answer

#5、#7です。

#7の説明で、一部、紛らわしいところがあったので、訂正します。

#7の(2)は、「予約語と同じ表名や列名、一部の特殊記号を含む表名や列名を使う場合」の説明でした。

chukenkenkou · Answer

MS-ACCESSで実機確認した人は、他のRDBMS利用者から見ると、やや不思議（カッコが異様に多かったり、同じ式を繰り返し書いたり）なSQLを提示してくれることがあります。
これは、以下のような理由からですので、参考までに。

(1)「TOP n」は、SQL Server、MS-ACCESSといったマイクロソフト製品の独自機能。
(2)予約語や一部の特殊記号を使う場合、標準SQLや主要なRDBMSでは、「"」で囲む。
　SQL Serverは、これに加え、[ ]を使える。
　MS-ACCESSは、[ ]で囲む。
　ちなみに、SQLiteでも、「"」に加え、 [ ]を使えるようです。
  さらにちなみに、MySQLでは「`」を使う。
(3)MS-ACCESSでは、インラインビュー等で式に付けた別名をORDER BYや検索条件に指定できないようであり、同じ式を繰り返し書かなければならないらしい。
　主要なRDBMSでは、内側のクエリで式に付けた別名を、外側のクエリで検索条件に指定できる。また、selectの選択リストで付けた別名を、ORDER BYでも指定できる。
(4)MS-ACCESSで生成されるクエリは、異様に( )が多くなる。また、JOIN等で他のRDBMSでは必要のないところにも、MS-ACCESSでは( )が必要なところがある。

fuuten_no_neko · Answer

改めて、TOPを使用しない方法
SELECT (SELECT Count(*)+1 FROM テーブル where テーブル.成績>s.成績
) AS 式1, s.成績
FROM テーブル AS s
WHERE ((((SELECT Count(*)+1 FROM テーブル where テーブル.成績>s.成績
))<=3))
ORDER BY s.成績;

fuuten_no_neko · Answer

＃１，＃３です。質問を充分読んでおりませんでした。拙解答は無視してください。失礼いたしました

fuuten_no_neko · Answer

＃１です
>TOP を使わない方法ありますでしょうか？
limitを使えばよろしいのでは。
要は
ORDER BY 100-[成績]
のような方法で何とかなりそうだと云うことです。

ann_dv · Answer

SQL Server 2000で試したので、構文は多少違うと思いますが、
select *
from テーブル名
where 成績 in (
select top 3 成績
from テーブル名 order by 成績 desc)
order by 成績

でうまくいきました。

降順で並び替えて昇順で受け取りたい

「TOP n」は、SQL Serverの独自機能であり、他の主要なRDBMSでは使えません。

＃６です。

>『) AS 式1, s.成績』 の 『式１』ってなんですか？

>最後の 『order by 成績』 の部分は必要なのでしょうか？

#5、#7です。

MS-ACCESSで実機確認した人は、他のRDBMS利用者から見ると、やや不思議（カッコが異様に多かったり、同じ式を繰り返し書いたり）なSQLを提示してくれることがあります。

改めて、TOPを使用しない方法

＃１，＃３です。

＃１です

SQL Server 2000で試したので、構文は多少違うと思いますが、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

>『) AS 式1, s.成績』　の　『式１』ってなんですか？

>最後の　『order by 成績』　の部分は必要なのでしょうか？