select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch from DIM

i have a customer table.
trans_id trans_date trans_amt debit_credit_indicator
001 01-JAN-13 1099 cr
001 12-JAN-13 500 db
002 24-FEB-13 400 db
002 23-MAR-13 345 cr
001 18-APR-13 800 cr
002 15-MAR-13 600 db
001 12-FEB-13 200 cr

i want like this output.

trans_id trans_amt debit_credit_indicator

i want get highest credit amount and lowest credit amount
and highest debit amount and lowest debit amount for each
trans_id.

Question Posted / ajit

2 Answers
6181 Views
Oracle, I also Faced
E-Mail Answers

Answer Posted / ajit

select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'cr'
and ac_no = 1
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'cr'
and ac_no = 2
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'db'
and ac_no = 2
group by ac_no, ac_branch
union
select ac_no, max(AC_STATUS) high,min(AC_STATUS) low,ac_branch
from DIM_ACCOUNt
where ac_branch = 'db'
and ac_no = 1
group by ac_no, ac_branch;

Is This Answer Correct ?

0 Yes

0 No

Post New Answer View All Answers

Please Help Members By Posting Answers For Below Questions

what are date and time intervals? : Sql dba

1034

what is the difference difference between procedure and packages

10364

What is the difference between pl and sql?

1076

Why do we need a foreign key?

1073

What is coalesce in sql?

1035

How many tables can a sql database have?

958

what are ddl statements in mysql? : Sql dba

1174

What is the difference between the conventional and direct path loader? : aql loader

1142

What is sp_helptext?

1052

What is mutating table error?

1157

How do you update sql?

955

What is a rank in sql?

940

What is not null in sql?

1036

What is cursor in pl sql with examples?

1011

Can we insert in sql function?

1063