Group by question
Group by question
(OP)
I have 2 tables:
Table 1: only had TranId - 4 transaction records
Table 2: TranId, Member Id - - 4 transaction records for same member in table 1
I would like to pick a transaction record group by the member id.
Please help me with query. I have tried below - but putting the transaction id in the record is the problem and dosent return 1 records:
select b.tran_id from table1 a
join table2 b on a.tran_id = b.trans_id
group by a.member_id, b.trans_unique_id
Table 1: only had TranId - 4 transaction records
Table 2: TranId, Member Id - - 4 transaction records for same member in table 1
I would like to pick a transaction record group by the member id.
Please help me with query. I have tried below - but putting the transaction id in the record is the problem and dosent return 1 records:
select b.tran_id from table1 a
join table2 b on a.tran_id = b.trans_id
group by a.member_id, b.trans_unique_id
RE: Group by question
Since you'r not using any of those, I'd try DISTINCT.
What result do you get if you remove the GROUP BY clause?
And then add DISTINCT.
select distinct b.tran_id from table1 a
join table2 b on a.tran_id = b.trans_id
group by a.member_id, b.trans_unique_id
RE: Group by question
-all tran_id are allready distinct
-what is common between them is the member id
so what i really need is a single record for a given member
anyother way to do it?
RE: Group by question
BTW, what is trans_unique_id??? That column is not included in your table description, so how are we expected to know what to do with it?
If you really need an answer, please give us working CREATE TABLE statements, and do also provide sample data. (To simplify, you can remove columns not read and not referenced.)
And do also give us sample data, and specify the wanted result.
RE: Group by question
key - tran_id - unique id for this table.
Columns:
tran_id
member_id
Table 2 (member table)
Columns:
member_id
Every time a member does a transcation like deposit, its saved in transaction table.
data-table 1:
tran_id, member_id
1,1
2,1
3,1
4,1
5,2
6,2
data-table2:
member_id
1
2
I have tried Max(tran_id) - but that would give me one id in whole of table. i need one transaction record per member
RE: Group by question
Since a member may have several different transaction id's, you have to pick one of them. I chose to use MAX to get the highest (latest?) transaction id.
SQL>create table t1 (tran_id int, member_id int);
SQL>insert into t1 values (1,1);
SQL>insert into t1 values (2,1);
SQL>insert into t1 values (3,1);
SQL>insert into t1 values (4,1);
SQL>insert into t1 values (5,2);
SQL>insert into t1 values (6,2);
SQL>create table t2 (member_id int);
SQL>insert into t2 values (1);
SQL>insert into t2 values (2);
SQL>select member_id, max(tran_id)
SQL&from t1
SQL&group by member_id;
member_id
=========== ===========
1 4
2 6
2 rows found
table 2 isn't needed as long as you do not want to list members without any transactions.
SQL>insert into t2 values (3);
SQL>select t2.member_id, max(tran_id)
SQL&from t2 left join t1 on t2.member_id = t1.member_id
SQL&group by t2.member_id;
member_id
=========== ===========
1 4
2 6
3 -
3 rows found
RE: Group by question
SQL>select member_id,
SQL& (select max(tran_id) from t1
where t1.member_id = t2.member_id)
SQL&from t2;
member_id
=========== ===========
1 4
2 6
3 -
3 rows found
RE: Group by question
select trans_id from (
select a.member_id, max(b.trans_id)as trans_unique_id
from table1 b join table2 a
on a.trans_id = b.trans_id
group by a.member_id
)d