subqueryのorder byを効かすには

Question

やりたいことは、
tableAとtableBとは1対nの関係で結合させて
tableB.cdの降順,　tableA.idの昇順で取得したい。
また、条件によってtableB.cdで降順したtableB最初の1件の場合と全件が必要です。

tableAはマスターでtableBにデータがあるデータが蓄積されcdの値が増えていきます。
よって、値が大きいものが最新となります。
取得する際はtableaに対してソートをしてもその際のtableBのデータは最新順（つまり降順）とさせたい。
また、条件（最新の1件のみのflagがあるない）によってtableBから取得するデータを1件or全件としたい。

SELECT *
FROM `tableA`
LEFT JOIN `tableB`
ON `tableA`.`id` = `tableB`.id
GROUP BY `tableA`.`id`
ORDER BY　`tableA`.`id` ASC, `tableB`.`cd` DESC

SELECT *
FROM (
	SELECT *
	FROM `tableA`
	LEFT JOIN `tableB`
	ON `tableA`.`id` = `tableB`.id
	ORDER BY `tableB`.`cd` DESC
) as `tableC`
GROUP BY `tableC`.`id`
ORDER BY `tableC`.`cd` ASC

こんな感じで試してみたのですがうまくいきません。

うまく行く方法はありますでしょうか？

CREATE TABLE `tablea` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customerName` varchar(60) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `tableb` (
  `cd` int(11) NOT NULL AUTO_INCREMENT,
  `id` int(11) NOT NULL,
  `data1` varchar(255) NOT NULL,
  PRIMARY KEY (`cd`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

INSERT INTO `tablea` (`id`, `customerName`) VALUES
(1, 'aaaa'),
(2, 'bbbb'),
(3, 'cccc');
(4, 'dddd');

INSERT INTO `tableb` (`cd`, `id`, `data1`) VALUES
(1, 1, 'hoge11'),
(2, 1, 'hoge12'),
(3, 1, 'hoge13'),
(4, 2, 'hoge21'),
(5, 2, 'hoge22'),
(6, 2, 'hoge22'),
(7, 3, 'hoge31');

期待する結果1

id	customerName	cd	id1	data1
1	aaaa		3	1	hoge13
1	aaaa		2	1	hoge12
1	aaaa		1	1	hoge11
2	bbbb		6	2	hoge23
2	bbbb		5	2	hoge22
2	bbbb		4	2	hoge21
3	cccc		7	3	hoge31
4	dddd		null	null	null

期待する結果2

id	customerName	cd	id1	data1
1	aaaa		3	1	hoge13
2	bbbb		6	2	hoge23
3	cccc		7	3	hoge31
4	dddd		null	null	null

UI側で"最新のみ"のチェックボックスがあるのでそれにチェックがあるかないかによって
期待する結果は1と2に分かれます。

また、プログラム側でwhere区をUI側で選択されたその他の条件で組み立てて行くつもりですが、
今回はそこは問題になってはいません。

最新flagがあるかないかで基本的なsql分は分けたくないなと思っています。
where区を組み立てるロジックをできればそれぞれに書きたくない。

Comments

[退会済みユーザー]

この手の質問では、CREATE文でテーブルの定義、サンプルデータのINSERT文を提示してくれると手元の環境でサクッと試して確度の高い回答を提示できますが、それがないと、質問文んを読み込んでいちいちテーブル定義から書き起こす必要があります。精度の高い回答を求める場合は、定義およびデータを提示してほしい。

[takagi.1994]

大変失礼しました。追記いたしました。

Answer 1

「同じIDで登録されたものの内の最新のCDのデータ」というを解釈しました。
相関副問合せで、同じIDで最大のCDを条件とします

select * 
from tableb as tb left join tablea as ta
     on tb.id=ta.id
where not exists(select 1 from tableb where id=tb.id and cd > tb.cd)
order by tb.id, tb.cd

または

select * 
from tableb as tb left join tablea as ta
     on tb.id=ta.id
where tb.cd=(select max(cd) from tableb where id = tb.id)
order by tb.id, tb.cd

Comments

[yambejp]

existsの後ろに余計な「=」が・・・

[sazi]

あ、ホントだ。コピーして書き換えたから・・
指摘ありがとうございます。

Answer 2

どんな結果を想定しているのか見えてきませんが、
単にcdとidのソート順が違うだけでは？

select * from tableA as t1
inner join tableB as t2 on t1.id=t2.id
order by t1.id asc,t2.cd desc

追記

もしcdの最大値だけとってid順に表示したということであればこう

select * from tableA as t1
inner join tableB as t2 on t1.id=t2.id
where not exists(select 1 from tableB where id=t2.id and cd>t2.cd)
order by t2.id asc

Answer 3

SUBQUERYにする必要がわかりません。

CREATE TABLE文が提示されたのでSELECT文を訂正しました。

SELECT DISTINCT
    B.`cd`
   ,A.`id`
  , A.`customerName`
  , B.`data1`
FROM `tableA` A
LEFT JOIN `tableB` B
ON A.`id` = B.`id`
ORDER BY B.`cd` DESC, A.`id`

Comments

[takagi.1994]

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

試してみましたが、`tablea`.`id` ASCが効かないようです。
また、この場合、tableB.cdの全件は取得できても降順の最初の1件のみが取得できません。

[Orlofsky]

CREATE TABLE文が提示されたのでSELECT文を訂正しました。

[takagi.1994]

group by A.idをつけるとb.cdが昇順となってしまい、期待する値（最新）ではなくなってしまいます。

[Orlofsky]

group byではなくDISTINCTにしました。
どんな出力結果が欲しいのか「質問」に追加しては？