プロが教えるわが家の防犯対策術!

添付画像のようなデータがあって、赤字のところだけ出力したいと
思っています。
グループIDごとに、一番個数が大きいもの(同じ個数の場合、個別IDが
若いもの)を出したいのです。
(順位はグループごとにふられているので、グループごと同じです)

個数が同じデータがあるもので、単純にMAXのものを…とするのも
難しそうで困っています。
ご教授いただけないでしょうか。

「グループ毎の最大値を持つデータのみ抽出し」の質問画像

A 回答 (3件)

質問通りにSQL作るとこんな感じ



SELECT MIN(t.個別ID) 個別ID, t.グループID, t.順位, t.個数
FROM table_1 t
INNER JOIN (SELECT a.グループID, MAX(a.個数) 個数 FROM dbo.Table_1 a GROUP BY a.グループID) ex
on ex.グループID=t.グループID
AND ex.個数 = t.個数
GROUP BY t.グループID, t.順位, t.個数
    • good
    • 2

テストしたら修正がありました。


例示のデータからご所望の結果が得られることを確認しました。

1)グループごとの個数最大値を求める(t1)
2)グループごとの個数最大値と同じ個数を持つ個別IDの最小値を求める(t2)
3)求めた最小値で列をSelect(t3)

select t3.*
from table1 as t3,
(
select table1.グループID, min(table1.個別ID) as 最小個別ID
from table1,
(
select グループID, max(個数) as 最大個数
from table1
group by グループID
) as t1
where table1.グループID = t1.グループID and table1.個数 = t1.最大個数
group by table1.グループID
) as t2
where t3.個別ID = t2.最小個別ID
    • good
    • 1

テストしてませんが。



こんな感じ?
1)グループごとの個数最大値を求める
2)グループごとの個数最大値と同じ個数を持つ個別IDの最小値を求める
3)求めた最小値の列をSelect


select t3.*
from table1 as t3,
(
select min(t2.個別ID) as 最小個別ID
from table1,
(
select グループID, max(個数) as 最大個数
from table1
group by グループID
) as t1
where t2.グループID = t1.グループID and t2.個数 = t1.最大個数
) as t2
where t3.個別ID = t2.最小個別ID
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

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

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

QMAX値を条件にデータを取得するには?

SQL文で困っています。
ご教授下さい。


下記のようなデータがあった場合、それぞれの区分毎に
年月が最大(最新)のデータを取得したいです。
(実際には1レコードにその他項目があり、それらも取得します。)
<検索対象データ>
区分 年月   金額
-----------------------------
A   200412  600
A   200503  560
B   200311  600
B   200508  1000
B   200504  560
C   200508  400
C   200301  1100


<取得したいデータ>

区分 年月   金額
-----------------------------
A   200503  560
B   200508  1000
C   200508  400

よろしくお願いします。

Aベストアンサー

テーブル名をXXXとすると次のようなSQLでよいと思います。(最善の方法かどうかは自信がないですが)

select B.* from (select 区分, max(年月) as 年月 from XXX group by 区分) As A
inner join XXX as B on A.区分 = B.区分 and A.年月 = B.年月
order by B.区分

Qグループ中で最大値を含む行を抽出するSQLはどう書けば良いでしょうか

列A,Bでグループ化し、グループ中で列Cが最大値の列を取得したいのです。
数百万件あるので極力低コストにしたく、
副問合せはINDEXが効かないと聞いたのですが、
それ以外思いつきませんでした。

副問合せしない方法はないでしょうか?
また、副問合せの中でコストの低いSQLはどんなものでしょうか。

■テーブルです
名前 型
CUST_ID CHAR(8)
BRANCH_ID CHAR(4)
VALID_DATE DATE
MYDATA VARCHAR2(20)

CUST_ID BRAN VALID_DA MYDATA
19740704 0000 06-01-01 comment0001
19740704 0000 05-12-01 comment0000
19740704 0000 06-02-01 comment0002
19740704 0001 06-01-01 comment0011
19740704 0001 06-03-01 comment0012

■このような結果が欲しいのですが。。。
CUST_ID BRAN VALID_DA MYDATA
19740704 0000 06-02-01 comment0002
19740704 0001 06-03-01 comment0012

■考えたSQLです(検証済)
select a.cust_id,a.branch_id,a.valid_date,a.mydata
from test a,(select cust_id,branch_id,max(valid_date) as mvd from test group by cust_id,branch_id) b
where
a.cust_id=b.cust_id and
a.branch_id=b.branch_id and
a.valid_date=b.mvd ;

select a.cust_id,a.branch_id,a.valid_date,a.mydata
from test a
where not exists
(select * from test b where a.cust_id=b.cust_id and a.branch_id < b.branch_id) ;

select cust_id,branch_id,valid_date,mydata
from test
where (cust_id,branch_id,valid_date) in (select cust_id,branch_id,max(valid_date) from test group by cust_id,branch_id) ;

select cust_id,branch_id,valid_date,mydata
from (select cust_id,branch_id,valid_date,mydata,max(valid_date) over(partition by cust_id,branch_id) as mvd from test)
where valid_date=mvd ;

列A,Bでグループ化し、グループ中で列Cが最大値の列を取得したいのです。
数百万件あるので極力低コストにしたく、
副問合せはINDEXが効かないと聞いたのですが、
それ以外思いつきませんでした。

副問合せしない方法はないでしょうか?
また、副問合せの中でコストの低いSQLはどんなものでしょうか。

■テーブルです
名前 型
CUST_ID CHAR(8)
BRANCH_ID CHAR(4)
VALID_DATE DATE
MYDATA VARCHAR2(20)

CUST_ID BRAN VALID_DA MYDATA
19740704 0000 06-01-01 comment0001
197407...続きを読む

Aベストアンサー

>副問合せしない方法はないでしょうか?

副問い合わせを使うから、必ずしも非効率なSQLであるとは
言い切れませんよ?

>副問合せの中でコストの低いSQLはどんなものでしょうか。

オプティマイザに聞かないと判りません。
素直に実行計画を出して、比較して下さい。

索引の有無やデータの分布など、いろいろな要素が絡み合って、
SQLの効率が変わります。
それらの情報無くして、効率的なSQLを選択するのは不可能です。

QSQL 特定のカラムが最大値のレコード取得

初心者です。SQL文で困っています。
どなたかご教授頂けたらと思います。

環境は、SQL SERVER 2000です。
特定のカラムが最大値のレコードのみ取得したいです。
たとえば、以下のようなデータがあるとします。
コード、履歴番号で主キーとします。
コード|履歴番号 |金額
0001 |1 |12000
0001 |2 |12001
0001 |3 |12002
0002 |1 |12000
0002 |2 |12001
0002 |3 |12002
0003 |1 |12000
0003 |2 |12001
この場合に、コード毎に履歴番号が最大のレコードのみを取得したいです。
理想の結果は以下になります。
コード|履歴番号 |金額
0001 |3 |12002
0002 |3 |12002
0003 |2 |12001

以上です。よろしくお願いいたします。

Aベストアンサー

SQL Server 2000で動くかどうか分かりませんが、SQL例です。

select *
from t1 as x
where 履歴番号=(select max(履歴番号)
from t1
where コード=x.コード)
order by コード

QSELECT 文 GROUP での1件目を取得

非常に初歩的な事で恐縮ですが、
以下のデータを抽出するsql文の書き方を模索しています。
環境:SQLSERVER2005

| 列1| 列2 |
+---+---+
| 1 | A |
| 1 | B |
| 1 | C |
| 1 | D |
| 2 | F |
| 2 | G |
| 2 | H |
| 3 | X |
| 3 | Y |
| 3 | Z |

上記のテーブルがあるとします。
列1でグループした値で、1レコード目の列2を抽出したいのです。
出力結果としては、

列1列2
+--+--+
1,A
2,F
C,X

としたいのです。
列1でGROUPしてしまうと列2の内容を集約しないといけないので困っています。
ご教授いたけないでしょうか?

Aベストアンサー

SQL Server 2005では、Oracleでいう分析関数が実装されています。

select
列名1,
列名2
from
(select
rank() over(partition by 列1 order by 列2) as rk,
列1,列2
from 表名) as x
where rk=1

QSQLで特定の項目の重複のみを排除した全項目を取得する方法

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のような構造になっている場合に「項目名1」について重複している項目を排除し、結果として


項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 B    え    イ    江

上のようなデータを取得したいのです。
この時に、Aの重複を排除して取得するレコードは1~3行目のどれでもよいです。
また、データを取得する際には必ずそのレコードの「全項目」を取得したいのでDistinctはうまく使えませんでした。

どなたか詳しい方、方法を教えてくださると幸いです。回答お待ちしております。

私は仕事上でデータベースを扱っていて、タイトルのような処理を行う必要があるのですが、いかんせん方法がわからずネット上を検索しても同様だったためここで質問させていただきます。

質問点を簡単に説明いたしますと、
たとえばAというテーブルがあって、

項目名1 項目名2 項目名3 項目名4
 A    あ    ア    亜
 A    い    ア    以
 A    う    ア    宇
 B    え    イ    江
 B    お    イ    尾

上のよ...続きを読む

Aベストアンサー

比較可能で一意性のある値をもてる項目6をテーブルに追加して、

select T.* from T, (select Item1,min(Item6) as Item6 from T group by item1) W where T.item6=W.item6;

――ってやるのが、一番手っ取り早いと思います。
他のところに影響がでないのであればですが。
oracleならrowidを使うとか、レコードの更新時刻を突っ込むとか。

QACCESS 一番最新の日付の金額を出したいのですが・・

社員ID/交通費/登録日 のフィールドがあるテーブルがあります。

社員ID/交通費/登録日
0001  800  2007/4/1
0002  400  2007/4/2


0001 700  2007/5/2

となった場合、0001の人は700円と言う風に登録日が最新のものを抽出したいのですが

クエリをつくり登録日の集計を「最大」にしてみたら、日付は5/2がちゃんと出ますが、金額がその登録日以外の金額になるのです。

どうすればよいでしょうか?
分かる方教えて下さい。宜しくお願いします。

 

Aベストアンサー

SQL文とクエリは同じです。

フィールド:登録日
テーブル:TEST
並び替え:
表示:
抽出条件:DLOOKUP("MAX(登録日)","TEST","社員ID=" & 社員ID);
または:

フィールド:登録日
テーブル:TEST
並び替え:
表示:
抽出条件:(SELECT MAX(登録日) FROM TEST AS B WHERE TEST.社員ID=B.社員ID)
または:

このように、クエリを編集すれば良いということです。
ですから、事実上は、"TEST" だけを実際のテーブル名、クエリ名に置き換えるだけです。

Q【Access】選択クエリのグループ化で最大日付の中の最大IDを抽出する方法?

「製品の販売状況が記録してあるテーブル」から、Accessの選択クエリのグループ化を利用して、「1つの製品番号に対し、最大日付の中の最大IDを抽出する」というアクションを行いたいのです。
例えば下の<テーブル例>だったら、
 ●製品100・・・2004/08/20 ID3 販売停止
 ●製品111・・・2004/05/10 ID6 出荷停止
を取り出したい訳です。

・製品番号(グループ化/昇順)
・日付(グループ化/昇順/最大)
・ID(グループ化/昇順/最大)
という選択クエリを組んだのですが、抽出結果は
 ●製品100・・・2004/08/20 ID9 販売停止
 ●製品111・・・2004/05/10 ID6 出荷停止
を選んでしまいます。
違うのに! そんな事したいんじゃないのに!(ToT)
どなたか詳しい方、助けてください。


<アクション>
製品の販売状況が記録してあるテーブルから、『最大の日付で最大のID』の「販売状況」を抽出したい。

<テーブル例>
製品番号 日付  ID販売状況
1002003/02/01  4終了
1002004/07/01  9出荷停止
1002004/08/01  2一時出荷停止
1002004/08/20  3販売停止
1002004/08/20  1終了
111 2003/01/15 5 終了
111 2004/05/10 6 出荷停止
111 2004/05/10 2 終了

「製品の販売状況が記録してあるテーブル」から、Accessの選択クエリのグループ化を利用して、「1つの製品番号に対し、最大日付の中の最大IDを抽出する」というアクションを行いたいのです。
例えば下の<テーブル例>だったら、
 ●製品100・・・2004/08/20 ID3 販売停止
 ●製品111・・・2004/05/10 ID6 出荷停止
を取り出したい訳です。

・製品番号(グループ化/昇順)
・日付(グループ化/昇順/最大)
・ID(グループ化/昇順/最大)
という選択クエリを組んだのですが、抽出結果は
 ●製...続きを読む

Aベストアンサー

ロジック的には No.1 の方の方法と同じことなんですが、Access 2000 のあたりから SQL の中にサブクエリーを記述できるようなので(記憶が定かではないのですが、Access97 まではできなかったような気がします。)、Access 2000 以降であれば、クエリーの SQL ビューに次のように記述すれば目的の抽出が可能です。
(テーブルを T_販売状況 としています。)

SELECT T3.製品番号, T3.日付, T3.MAXID, T4.販売状況
FROM T_販売状況 AS T4 INNER JOIN (
SELECT T1.製品番号, T1.日付, MAX(T1.ID) AS MAXID
FROM T_販売状況 AS T1 INNER JOIN (
SELECT 製品番号, MAX(日付) AS MAX日付
FROM T_販売状況
GROUP BY 製品番号) AS T2
ON ( ( T1.製品番号=T2.製品番号) AND (T1.日付=T2.MAX日付) )
GROUP BY T1.製品番号, T1.日付) AS T3
ON ( T3.製品番号 = T4.製品番号 AND T3.日付 = T4.日付 AND T3.MAXID = T4.ID )

ロジック的には No.1 の方の方法と同じことなんですが、Access 2000 のあたりから SQL の中にサブクエリーを記述できるようなので(記憶が定かではないのですが、Access97 まではできなかったような気がします。)、Access 2000 以降であれば、クエリーの SQL ビューに次のように記述すれば目的の抽出が可能です。
(テーブルを T_販売状況 としています。)

SELECT T3.製品番号, T3.日付, T3.MAXID, T4.販売状況
FROM T_販売状況 AS T4 INNER JOIN (
SELECT T1.製品番号, T1.日付, MAX(T1.ID) AS MAXID
FR...続きを読む

QSELECTで1件のみ取得するには?

こんにちわ。
いまORACLE9iを使用している者です。

ACCESSでは
SELECT TOP 1 項目名 FROM テーブル名
ORDER BY 項目名;
で並べ替えたデータ群のうち,先頭の1件だけを
取ることができますが,
ORACLEでそのような機能(SQL)はあるでしょうか?
教えてください。
よろしくお願いします。

Aベストアンサー

order by と rownum を併用する場合は注意が必要です。

[tbl01]
cola | colb
------------
1000 | aaaa
1001 | bbbb

というデータがある場合、
select cola from tbl01 where rownum < 1 order by cola desc;
とすると、「1001」ではなく、「1000」が返されます。
これは、order by の前に rownum < 1 が適用されてしまうからです。

解決するには、
select aaa from (select cola aaa from tbl01 order by cola desc) where rownum = 1;
とすれば良いです。

Q最大値を含むレコードの抽出

あるテーブルから、最大値を持つレコードのキーと最大値だけでなく、そのレコードの値を出したいと思います。
最大値をもつレコードを抽出して、元のレコードと結合するしか方法はないのかなと思い、類似した質問があるような気もするのですが、条件が異なるとうまく行かないため、自分なりに考えてみました。
データベースはDB2です。

SELECT a.倉庫,a.品目,a.単価 FROM 在庫マスタ as a
where (a.品目,a.単価) in (select b.品目, max(b.単価)
from 在庫マスタ as b group by b.品目)
つまり、副問合せするときの結合キーが複数あるとSQLがエラーになってしまうようなのです。

結合キーが2つ以上ある時、このようなパターンの対処方法は無いでしょうか?

Aベストアンサー

SELECT a.倉庫,a.品目,a.単価 FROM 在庫マスタ as a
where a.単価 in (select max(b.単価)
from 在庫マスタ as b where b.品目=a.品目)
でいいんじゃないの

Qキー毎の、ある列のmaxのレコードを取得し、別の列の値を取得したい

下記のようなテーブルがあった場合に、

ID1|ID2|価格
-----------------------
1 | 1 | 100
1 | 2 | 200
1 | 3 | 150
2 | 1 | 1000
2 | 2 | 500
3 | 1 | 50
3 | 2 | 100

下記のようなレコードを返してくるSQLを教えて下さい。
ID1 | ID2 | 価格
-----------------------
1 | 3 | 150
2 | 2 | 500
3 | 2 | 100

要するに、ID1毎に、ID2が最大のレコードを取得し、価格列の数値を使いたいのです。
基本的なSQLは理解しているつもりなのですが・・・。

ご回答よろしくお願いします。

Aベストアンサー

SQL> select * from test;

   ID1    ID2   XXX
---------- ---------- ----------
    1     1    100
    1     2    200
    1     3    150
    2     1    1000
    2     2    500
    3     1     50
    3     2    100

7行が選択されました。

SQL> select ID1
 2    ,max(ID2) as ID2
 3    ,max(XXX)keep(dense_rank Last order by ID2) as XXX
 4  from test
 5  group by ID1;

    ID1    ID2    XXX
---------- ---------- ----------
     1     3     150
     2     2     500
     3     2     100

SQL>

SQL> select * from test;

   ID1    ID2   XXX
---------- ---------- ----------
    1     1    100
    1     2    200
    1     3    150
    2     1    1000
    2     2    500
    3     1     50
    3     2    100

7行が選択されました。

SQL> select ID1
 2    ,max(ID2) as ID2
 3    ,max(XXX)keep(dense_rank Last order by ID2) as XXX
 4  from test
...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング