Group by question

[JazzMaan]

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

 

[JarlH]

GROUP BY us typically used together with aggregate functions (max, min, count, avg).

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.

[tt]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[/tt]

 

[JazzMaan]

Distinct wont work me as:
-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?

 

[JarlH]

Have you tried to use an aggregate function, i.e. MAX(b.tran_id), to get just one of a member's different transaction id's?



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.

 

[JazzMaan]

Table 1 (transaction table)
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

 

[JarlH]

Does this help?

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.
[tt]
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[/tt]

table 2 isn't needed as long as you do not want to list members without any transactions.
[tt]
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 [/tt]

 

[JarlH]

Another way is to use a sub-query:
[tt]
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[/tt]

 

[JazzMaan]

Thanks JarlH with your help i solved it in this way:


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