Selecting Maximum Value from 3 different tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting Maximum Value from 3 different tables

Author

Topic

shajeerkt
Starting Member

4 Posts

Posted - 2009-02-17 : 03:14:30

Hi guys,

I am pretty new to this forum and SQL. I got a problem while writing a query.Please help me to sort this out.

I have three tables SUB_RAT,SUB_AGE,SUB_GEN. In three tables i have a common column RAT_EN.And in Each table i have Sub_total1,Sub_total2,Sub_total3 respectively.

I have to Select the Distinct RAT_EN from Each table(As three tables have same values in RAT_EN) and its corrosponding MAX VALUE from Sub_total1,Sub_total2,Sub_total3.

For eg;

RAT_EN SUB_TOTAL1 SUB_TOTAL2 SUB_TOTAL3
car 1000 1500 1487
jeep 650 800 478
bus 4210 414 4514

Hope you understand my question... If any one want more clarification please ask.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-17 : 04:50:43

Try this:

Select a.*,b.sub2,c.sub3 from
(SELECT distinct [RAT_EN]
,max([Sub_total1])as sub1
FROM SUB_RAT
group by rat_en) as a

JOIN
(
SELECT distinct [RAT_EN]
,max([Sub_total2])as sub2
FROM SUB_AGE
group by rat_en) as b

on a.rat_en=b.rat_en
JOIN
(
SELECT distinct [RAT_EN]
,max([Sub_total3])as sub3
FROM SUB_GEN
group by rat_en) as c

on b.rat_en=c.rat_en

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 05:08:10

try this also


SELECT a.RAT_EN,a.subtotal1 ,(SELECT MAX(Sub_total2) FROM SUB_AGE WHERE rat_en=a.rat_en)AS subtotal2,
(SELECT MAX(Sub_total2) FROM SUB_GEN WHERE rat_en=a.rat_en) AS subtotal3
FROM SUB_RAT AS a

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 05:46:29

quote:
Originally posted by darkdusky

Try this:

Select a.*,b.sub2,c.sub3 from
(SELECT distinct [RAT_EN]
,max([Sub_total1])as sub1
FROM SUB_RAT
group by rat_en) as a

JOIN
(
SELECT distinct [RAT_EN]
,max([Sub_total2])as sub2
FROM SUB_AGE
group by rat_en) as b

on a.rat_en=b.rat_en
JOIN
(
SELECT distinct [RAT_EN]
,max([Sub_total3])as sub3
FROM SUB_GEN
group by rat_en) as c

on b.rat_en=c.rat_en

Whats the point of having distinct there ??

select 
	RAT_EN,MAX_SUB_TOTAL1=max(SUB_TOTAL1),MAX_SUB_TOTAL2=max(SUB_TOTAL2),MAX_SUB_TOTAL3=max(SUB_TOTAL3) 
from (
	select RAT_EN,SUB_TOTAL1,null SUB_TOTAL2,null SUB_TOTAL3
	from
	SUB_RAT
	
	union all
	
	select RAT_EN,null,SUB_TOTAL2,null 
	from
	SUB_AGE
	
	union all
	
	select RAT_EN,null,null,SUB_TOTAL3
	from
	SUB_GEN
      )s
GROUP BY
RAT_EN

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 05:54:14

quote:

Originally posted by bklr

try this also


SELECT a.RAT_EN,a.subtotal1 ,(SELECT MAX(Sub_total2) FROM SUB_AGE WHERE rat_en=a.rat_en)AS subtotal2,
(SELECT MAX(Sub_total2) FROM SUB_GEN WHERE rat_en=a.rat_en) AS subtotal3
FROM SUB_RAT AS a

Not sure if this is correct. Whats happening with subtotal1 there ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 05:56:26

i forgot to write subquery in from clause


SELECT a.RAT_EN,a.subtotal1 ,(SELECT MAX(Sub_total2) FROM SUB_AGE WHERE rat_en=a.rat_en)AS subtotal2,
(SELECT MAX(Sub_total2) FROM SUB_GEN WHERE rat_en=a.rat_en) AS subtotal3
FROM (SELECT [RAT_EN]
,max([Sub_total1])as subtotal1 
FROM SUB_RAT
group by rat_en)AS a

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-17 : 06:04:45

quote:
Originally posted by bklr

i forgot to write subquery in from clause
SELECT a.RAT_EN,a.subtotal1 ,(SELECT MAX(Sub_total2) FROM SUB_AGE WHERE rat_en=a.rat_en)AS subtotal2,
(SELECT MAX(Sub_total2) FROM SUB_GEN WHERE rat_en=a.rat_en) AS subtotal3
FROM (SELECT distinct [RAT_EN]
,max([Sub_total1])as sub1
FROM SUB_RAT
group by rat_en)AS a

Go through this again, I still don't think its correct.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 09:20:21

quote:
Originally posted by shajeerkt

Hi guys,

I am pretty new to this forum and SQL. I got a problem while writing a query.Please help me to sort this out.

I have three tables SUB_RAT,SUB_AGE,SUB_GEN. In three tables i have a common column RAT_EN.And in Each table i have Sub_total1,Sub_total2,Sub_total3 respectively.

I have to Select the Distinct RAT_EN from Each table(As three tables have same values in RAT_EN) and its corrosponding MAX VALUE from Sub_total1,Sub_total2,Sub_total3.

For eg;

RAT_EN SUB_TOTAL1 SUB_TOTAL2 SUB_TOTAL3
car 1000 1500 1487
jeep 650 800 478
bus 4210 414 4514

Hope you understand my question... If any one want more clarification please ask.

if sql 2005:-


SELECT t1.RAT_EN,
       MAX(t1.Sub_total1) AS Sub_total1,
       t2.Sub_total2,
       t3.Sub_total3
FROM SUB_RAT t1
OUTER APPLY (SELECT TOP 1 Sub_total2
             FROM SUB_AGE
             WHERE RAT_EN=t1.RAT_EN
             ORDER BY Sub_total2 DESC) t2
OUTER APPLY (SELECT TOP 1 Sub_total3
             FROM SUB_GEN
             WHERE RAT_EN=t1.RAT_EN
             ORDER BY Sub_total3 DESC)t3
GROUP BY t1.RAT_EN,
       t2.Sub_total2,
       t3.Sub_total3

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-17 : 11:56:35

sakets_2000 - your right I didn't need a distinct because the max should return a single value - I think DISTINCT shouldn't actually impair the query performanace in this case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-17 : 12:02:45

quote:
Originally posted by darkdusky

sakets_2000 - your right I didn't need a distinct because the max should return a single value - I think DISTINCT shouldn't actually impair the query performanace in this case.

not only max() but also group by. nope use of distinct will simply cause extra processing to check for distinct values.

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-02-18 : 04:34:48

I did a little test of some of different methods with 100000 rows in each of the tables with no keys or indexes in any of the tables:
darkdusky method : CPU time = 31 ms, elapsed time = 26 ms.
darkdusky method with distinct removed : CPU time = 31 ms, elapsed time = 26 ms.
(Both have exact same Execution Plan, i.e. DISTINCT is ignored since Max is finding a single distinct value)
bklr method: CPU time = 16 ms, elapsed time = 46 ms.
visakh method: CPU time = 47 ms, elapsed time = 83 ms.

shajeerkt
Starting Member

4 Posts

Posted - 2009-02-21 : 04:21:59

Hi guys,

I tried With the below SQL. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS

SELECT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE,
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN
FROM UP_AALZEER.PS_V_SUB_AGE AS A GROUP BY A.RAT_EN

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-21 : 04:58:55

[code]
SELECT DISTINCT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE,
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN
FROM UP_AALZEER.PS_V_SUB_AGE AS A
[/code]

shajeerkt
Starting Member

4 Posts

Posted - 2009-02-21 : 05:07:46

quote:

Originally posted by visakh16


SELECT DISTINCT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE, 
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN 
FROM UP_AALZEER.PS_V_SUB_AGE AS A

Still showing the same error with the above modification...

" Syntax error: expected something between '(' and the 'SELECT' keyword."

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-21 : 05:30:24

use this,

select 
	RAT_EN,MAX_SUB_TOTAL1=max(SUB_TOTAL1),MAX_SUB_TOTAL2=max(SUB_TOTAL2),MAX_SUB_TOTAL3=max(SUB_TOTAL3) 
from (
	select RAT_EN,SUB_TOTAL1,null SUB_TOTAL2,null SUB_TOTAL3
	from
	SUB_RAT
	
	union all
	
	select RAT_EN,null,SUB_TOTAL2,null 
	from
	SUB_AGE
	
	union all
	
	select RAT_EN,null,null,SUB_TOTAL3
	from
	SUB_GEN
      )s
GROUP BY
RAT_EN

shajeerkt
Starting Member

4 Posts

Posted - 2009-02-21 : 05:47:11

quote:

Originally posted by sakets_2000

use this,

select 
	RAT_EN,MAX_SUB_TOTAL1=max(SUB_TOTAL1),MAX_SUB_TOTAL2=max(SUB_TOTAL2),MAX_SUB_TOTAL3=max(SUB_TOTAL3) 
from (
	select RAT_EN,SUB_TOTAL1,null SUB_TOTAL2,null SUB_TOTAL3
	from
	SUB_RAT
	
	union all
	
	select RAT_EN,null,SUB_TOTAL2,null 
	from
	SUB_AGE
	
	union all
	
	select RAT_EN,null,null,SUB_TOTAL3
	from
	SUB_GEN
      )s
GROUP BY
RAT_EN

What is MAX_SUB_TOTAL1,MAX_SUB_TOTAL2,MAX_SUB_TOTAL3 and s here

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-21 : 06:00:52

quote:

Originally posted by shajeerkt

quote:

Originally posted by sakets_2000

use this,

select 
	RAT_EN,MAX_SUB_TOTAL1=max(SUB_TOTAL1),MAX_SUB_TOTAL2=max(SUB_TOTAL2),MAX_SUB_TOTAL3=max(SUB_TOTAL3) 
from (
	select RAT_EN,SUB_TOTAL1,null SUB_TOTAL2,null SUB_TOTAL3
	from
	SUB_RAT
	
	union all
	
	select RAT_EN,null,SUB_TOTAL2,null 
	from
	SUB_AGE
	
	union all
	
	select RAT_EN,null,null,SUB_TOTAL3
	from
	SUB_GEN
      )s
GROUP BY
RAT_EN

What is MAX_SUB_TOTAL1,MAX_SUB_TOTAL2,MAX_SUB_TOTAL3 and s here

they are alias name for subtotals and s is alias name for derived table

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-21 : 08:39:56

quote:
Originally posted by shajeerkt

Hi guys,

I tried With the below SQL. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS

SELECT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE,
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN
FROM UP_AALZEER.PS_V_SUB_AGE AS A GROUP BY A.RAT_EN

Look like you are not using SQL Server. Post it in other forum which supports TeraData.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-22 : 01:01:40

quote:
Originally posted by sodeep

quote:
Originally posted by shajeerkt

Hi guys,

I tried With the below SQL. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS

SELECT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE,
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN
FROM UP_AALZEER.PS_V_SUB_AGE AS A GROUP BY A.RAT_EN

Look like you are not using SQL Server. Post it in other forum which supports TeraData.

whats TeraData? another RDBMS?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-22 : 08:21:22

quote:
Originally posted by visakh16

quote:
Originally posted by sodeep

quote:
Originally posted by shajeerkt

Hi guys,

I tried With the below SQL. But it was showing some error "Syntax error: expected something between '(' and the 'SELECT' keyword." I am using a Tera DBMS

SELECT A.RAT_EN,
(SELECT max(Sub_total1) FROM UP_AALZEER.PS_V_SUB_RAT WHERE RAT_EN=A.RAT_EN) AS MAX_RAT,
(SELECT max(Sub_total2) FROM UP_AALZEER.PS_V_SUB_AGE WHERE RAT_EN=A.RAT_EN) AS MAX_AGE,
(SELECT max(Sub_total3) FROM UP_AALZEER.PS_V_SUB_GEN WHERE RAT_EN=A.RAT_EN) AS MAX_GEN
FROM UP_AALZEER.PS_V_SUB_AGE AS A GROUP BY A.RAT_EN

Look like you are not using SQL Server. Post it in other forum which supports TeraData.

whats TeraData? another RDBMS?

Yes,It is prone to Datawarehouse environment.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources