MYSQLで重複せずにカウント上位から取得したい

質問

お世話になります。

MYSQLにて以下のようなtableがあるとします。

id ex_id name count flag time memo
1 1 山田 100 on 10:11 テスト
2 1 山田 80 on 10:12 テスト2
3 2 田中 50 on 10:13 テスト3
4 3 鈴木 30 on 10:14 テスト4
5 3 鈴木 110 on 10:14 テスト5
6 3 鈴木 10 on 10:14 テスト6

このうち、countの数字が高いものから表示したいのですが、
ex_idが重複する場合はその数字が最も高いもののみ表示したく考えています。
目的とした表示結果順は以下です。

id ex_id name count flag time memo
5 3 鈴木 110 on 10:14 テスト5
1 1 山田 100 on 10:11 テスト
3 2 田中 50 on 10:13 テスト3

distinctやgroup byなど色々考えてみたのですが、思ったような挙動になりませんでした。
以上、よろしくお願い致します。

回答No.4

Siegrune

select * from tbl t1
where count =
(select max(count) from tbl t2 where t1.ex_id = t2.ex_id)
order by count desc

ではどうでしょう？

回答No.3

maiko0318

ex_id, countでindexを作ればどうなるでしょう？

お礼

回答ありがとうございます。
indexは既にしてあるのですが、ダメでした。

回答No.2

yambejp

#1さんの補足ですが、とくにconcatする必要はありません

create table tbl(id int,ex_id int,name varchar(20),count int,flag set('on','off') default 'on',timex time,memo text);
insert into tbl values(1,1,'山田',100,'on','10:11','テスト'),(2,1,'山田',80,'on','10:12','テスト2'),(3,2,'田中',50,'on','10:13','テスト3'),(4,3,'鈴木',30,'on','10:14','テスト4'),(5,3,'鈴木',110,'on','10:14','テスト5'),(6,3,'鈴木',10,'on','10:14','テスト6');

select * from tbl
where (ex_id,count) in
(select ex_id,max(count) from tbl group by ex_id)
order by count desc

お礼

回答ありがとうございます。
処理としまして、サンプル程度のデータ数なら良いのですが、30,000件程度あるテーブルで実行すると重すぎてダウンしてしまうようです。

limitを10程度にしてみても、実行時間が10秒近くかかってしまいました。
何か良い方法は無いでしょうか？

回答No.1

maiko0318

DB2ですと、こうなります。
I:\>db2 select * from Mysql

ID EX_ID NAME COUNT FLAG TIME MEMO
-- ----- ---------- ----------- ---- ----- --------------------
1 1 山田 100 on 10:11 テスト
2 1 山田 80 on 10:12 テスト2
3 2 田中 50 on 10:13 テスト3
4 3 鈴木 30 on 10:14 テスト4
5 3 鈴木 110 on 10:14 テスト5
6 3 鈴木 10 on 10:14 テスト6

6 レコードが選択されました。

I:\>db2 select * from Mysql where concat(ex_id, count) in(select distinct concat(ex_id, max(count)) from Mysql group by ex_id) order by count desc

ID EX_ID NAME COUNT FLAG TIME MEMO
-- ----- ---------- ----------- ---- ----- --------------------
5 3 鈴木 110 on 10:14 テスト5
1 1 山田 100 on 10:11 テスト
3 2 田中 50 on 10:13 テスト3

3 レコードが選択されました。

concatがあればいいのですが・・・

お礼

回答ありがとうございます。
処理としまして、サンプル程度のデータ数なら良いのですが、30,000件程度あるテーブルで実行すると重すぎてダウンしてしまうようです。

limitを10程度にしてみても、実行時間が10秒近くかかってしまいました。
何か良い方法は無いでしょうか？