Subquery using MAX subquery need Help

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

Subquery using MAX subquery need Help

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-27 : 12:47:28

Hi Team:

SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, e.sample_date, d.station_name
from group_detail a
Inner Join station_group b on a.group_code = b.group_code
Inner Join station_type c on b.station_nbr = c.station_nbr
Inner JOIN station d ON c.station_nbr = d.station_nbr
Inner JOIN gas_analysis e ON d.station_nbr = e.station_nbr
Where (a.group_type = 'NPN')and( b.end_eff_date >=@sampledateBegin)and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))
and (e.sample_date Between @sampledateBegin and @sampledateEnd )
and e.seq_nbr =
(select max(x.seq_nbr)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date )
and e.C02_pct =
(Select max(x.C02_pct)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date
and x.seq_nbr= e.seq_nbr)
order by a.group_code

 
Group    station_nbr   Beg_date     end_date     C02     sample_date
0004     D01G000819    2007-09-01   3000-12-31 	 0.40    2007-10-02
0004     D01G000819    2007-09-01   3000-12-31 	 2.49	 2007-10-09

I am trying to obtain the highest C02 in this case 2.49 but before getting the highest C02 it needs to meet the Select MAX seq_nbr within a given date. In other words I am trying to write a Subquery that first will obtain the MAX seq_nbr within a given date and based on the records pick the higest C02.

I appreciate the help. I hope my scope help understanding what I am trying to achieve.

karthik.shanmugavelu
Starting Member

3 Posts

Posted - 2007-11-28 : 01:03:32

hi ...
i had question in mind after reading ur query,you want to have greatest co2 rite. then u can go for that query first,which inturn will give the value of e.seq_nbr.
hope u got an idea.

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-28 : 04:36:19

try this
declare @variable1 datatype
declare @variable2 datatype

select @variable1=max(x.seq_nbr)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date

Select @variable2=max(x.C02_pct)
From gas_analysis x
Where x.station_nbr = b.station_nbr
and x.sample_date= e.sample_date
and x.seq_nbr= e.seq_nbr

finally
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name ,
b.station_nbr, b.beg_eff_date, b.end_eff_date,
Round( e.C02_pct,3)As C02_pct, e.sample_date, d.station_name
from group_detail a
Inner Join station_group b on a.group_code = b.group_code
Inner Join station_type c on b.station_nbr = c.station_nbr
Inner JOIN station d ON c.station_nbr = d.station_nbr
Inner JOIN gas_analysis e ON d.station_nbr = e.station_nbr
Where (a.group_type = 'NPN')and( b.end_eff_date >=@sampledateBegin)and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP'))
and (e.sample_date Between @sampledateBegin and @sampledateEnd )
and e.seq_nbr =@variable1
and e.C02_pct = @variable2
order by a.group_code

we cann't compare result set of a query to some value directly as you was trying

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources