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

質問

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

という結果が欲しいのです。

よろしくおねがいします。

回答No.5

chukenkenkou · ベストアンサー

「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 成績
;

回答No.11

noname#182251

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

お礼

ありがとうございました。

回答No.10

chukenkenkou

>『) 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位タイまで）を最終的な検索結果として得ています。

お礼

「もしかして名前を入れればいいのかな？」と思って入れたのですがうまくいかなかったので質問しました。
でも入力ミスだったんでしょうね。
今日再度チャレンジすると期待する結果が得られました。

解説までしていただきましてありがとうございます。
よくわかりました。

回答No.9

chukenkenkou

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

ほぼすべてのRDBMSは、「ORDER BY」指定がないと、検索結果の返却順序は保証していません。

「保証していないと言っているが、実はプライマリキー順や格納順になるのでは？」と考える人もいるようですが、例えば格納と削除を繰り返したり、プライマリキーの逆順やランダムな格納をすると、すぐに検索結果の順序が乱れたりします。

ジョイン、group byなどを行う場合、内部的にソートされることもありますが、その結果はRDBMS側としては保証されていませんから、内部処理が変わって順序が不定になっても、誰にも文句を言えません。

お礼

そうですね、検索結果の順序は保証していないと見たことがありました。

ありがとうございます。

回答No.8

chukenkenkou

#5、#7です。

#7の説明で、一部、紛らわしいところがあったので、訂正します。

#7の(2)は、「予約語と同じ表名や列名、一部の特殊記号を含む表名や列名を使う場合」の説明でした。

お礼

詳しく教えていただき、ありがとうございます。

試してみた結果、期待通りの結果が得られました。

私のレベルではとても高度な回答だったため、理解に時間がかかってしまいました。。。

それでもうひとつお伺いしたいのですが、
最後の　『order by 成績』　の部分は必要なのでしょうか？
『where rownum <= 3』　で終わっても同じ結果が得られるのでは？と思ったのですが…。

よろしければ最後の一行を付ける意味教えてください。

回答No.7

chukenkenkou

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では( )が必要なところがある。

回答No.6

noname#182251

改めて、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.成績;

お礼

回答ありがとうございます。

勉強不足ですみません。お伺いしたいのですが、
『) AS 式1, s.成績』　の　『式１』ってなんですか？

回答No.4

noname#182251

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

回答No.3

noname#182251

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

回答No.2

ann_dv

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

でうまくいきました。

お礼

早速の回答ありがとうございます。

sqlite では TOP が使えないようです。。。

TOP を使わない方法ありますでしょうか？

回答No.1

noname#182251

SELECT TOP 3 成績, 100-[成績] AS s
FROM テーブル
ORDER BY 100-[成績];

みたいなSQLで如何でしょう。Accessで試したのでTOPを使用しています。

お礼

早速の回答ありがとうございます。

sqlite では TOP が使えないようです。。。

TOP を使わない方法ありますでしょうか？