×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Group by question

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
 

RE: Group by question

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.

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

(OP)
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?

RE: Group by question

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.

RE: Group by question

(OP)
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






 

RE: Group by question

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.

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

Another way is to use a sub-query:

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

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close