| Author |
Topic |
|
dbenoit64
Starting Member
36 Posts |
 Posted - 2003-07-22 : 10:50:42
|
| there are 2 problems with this query:select DATABASEST storm_num, max(maxwind_kt) max_wind_speed, datediff(dd, min(convert(datetime, [date],104)), (max(convert(datetime, [date],104)))) duration_days, count(*) as num_tracks, (SELECT TOP 1 [NAME] FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) storm_name, (SELECT TOP 1 PRESSURE_M FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) PRESSURE_M, (SELECT TOP 1 [date] FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) storm_date, (SELECT TOP 1 hour_utc FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) hour_utc, (SELECT TOP 1 saffirsimp FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) ss_scale, (SELECT TOP 1 yearlystor FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) year_storm_num, (SELECT TOP 1 comment FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST Order by maxwind_kt desc, [date], hour_utc ) comment, (SELECT top 1 round(avg(systemspee),1) FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST and lastpositi = 0 ) avg_trans_speed from NHCBestTracks1851To2001$ s where RECORDNUMB in (32980,22280,19450,11335,6887,5185,2282,25225,30091,24776,4316,32979,21695,21115,19111,15754,8725,8138,5200,3617,17427,7796,6519,6438,30090,25224,14068,11334,28394,19776,17007,24075,21427,15753,14956,8137,5538,31596,25223,8724,7478,507,26917,6518,18113,16305,15669,5201,3618,2590,31595,28937,8399,5537,1478,26836,21709,19451,16304,15670,7479,6439,18112,13576,8215,26835,21116,17160,7797,2591) and SAFFIRSIMP in ('SS1','SS2','SS3','SS4','SS5','TD','TS') and COMMENT in ('*') the first is that i would love to put all the subqueries together but when i do, i get an error that says you can return more than one column in a subquery. this causes me to have to write all this extra crap,the second is that the final where clause does not effect the subqueries. this means that to make this work the way i want it to i would have to repeat the where clause 8 times. this is very redundant and i wish there was a way around it. if it was possible to put the subqueries together i would only have to repeat the final where clause once. that would be ideal for me.any ideas would be greatly appreciated.thanks |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-07-22 : 11:19:44
|
quote:
there are 2 problems with this query:
2?????Didn't we do this one also?I would highly reccomend breaking this up in to 2 components.One is going after a single set of values, while the other seems like a set.Don't combine them until after you get your results.Also, SELECT TOP 1 with an Order by is really a predicate where the values of those columns are the MIN(col)DECLARE @maxwindkt varchar(25), @myDate datetime, @hour_utc int, @stormName varchar(25)SELECT @maxwindkt = MIN(maxwindkt) , @myDate = MIN([Date]) , @hour_utc = MIN(hour_utc) , @stormName = MIN(stormName) Then use them in 1 Select for all of thos SELECT (SELECT statements.It should be 1 statement.Brett8-) |
 |
|
|
|
|
|
subqueries