coorelated sub query with group by

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

SQL Server 2000 Forums

Transact-SQL (2000)

coorelated sub query with group by

Author

Topic

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-07 : 20:18:15

I've got an sql statement that ive developed for an application which has been giving back incorrect results for the past few months and ive finally figured out why but cant figure out how to fix it. Anyways, Here it is:

*****************************

SELECT DATABASEST AS storm_num, MAX(MAXWIND_KT) AS max_wind_speed, DATEDIFF(dd, MIN(CONVERT(datetime, [DATE], 104)), MAX(CONVERT(datetime,
[DATE], 104))) AS 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) AS storm_name,
(SELECT TOP 1 PRESSURE_M
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS PRESSURE_M,
(SELECT TOP 1 [date]
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS storm_date,
(SELECT TOP 1 hour_utc
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS hour_utc,
(SELECT TOP 1 saffirsimp
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS ss_scale,
(SELECT TOP 1 yearlystor
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS year_storm_num,
(SELECT TOP 1 comment
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS comment,
(SELECT TOP 1 AVG(systemspee)
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST AND lastpositi = 0) AS avg_trans_speed
FROM dbo.NHCBestTracks1851To2001$ s
WHERE (DATABASEST in (223)) AND (COMMENT in ('*'))
GROUP BY DATABASEST

******************************

The info thats comming back is correct :

storm_num, max_wind_speed, duration_days and num_tracks.

the incorrect data are:

storm_name, pressure_M, storm_date, hour_utc, ss_scale, year_storm_num, comment, avg_trans_speed.

The reason is because the Where statement at the end ie "WHERE (DATABASEST = 223) AND (COMMENT = '*')...." does not effect the rows that I just mentioned. I want to figure out a way to have the overall WHERE (the one at the end of entire statment) to effect these results.

The tricky thing is that I cant put the where stuff right up in the coorelated selects (i think thats what they are called) because All that part is hard coded.

See I generate this staement on the fly. that is i add stuff to the end where clause on the fly in an asp page. I could have other things in there at the end like Where commment in ('*','E')...

Note:
There can be other things in the where clause like a date range. (Where date between 11/11/11 and 12/12/12).

This might be totally easy to figure out but IM no sql expert. But I really really need to get this fixed.
I hope someone can figure this out.

Thanks in advance

Dave

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 00:27:00

Can you explain better what you mean by "Hard Coded"? I'm not clear if part of the query must remain fixed, and only part rewritten. These hard-coded parts you mention are not working correctly must be rewritten... (Don't want to be difficult.)

If the storm number DATABASEST uniquely identifies the storm (this is where table structure and sample data would be very useful to post with your question), then your query ought to be working fine since DATABASEST does equal 223 for all the subquerys (unless COMMENT = '*') needs to be involved to get it right.

If you post some sample data (5 rows ought to do from NHCBestTracks......) there are lots of us who will take a stab at correcting the query.

Sam

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-08 : 13:13:44

Isn't all of this

[qoute]
(SELECT TOP 1 PRESSURE_M
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS PRESSURE_M,
(SELECT TOP 1 [date]
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS storm_date,
(SELECT TOP 1 hour_utc
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS hour_utc,
(SELECT TOP 1 saffirsimp
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS ss_scale,
(SELECT TOP 1 yearlystor
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS year_storm_num,
(SELECT TOP 1 comment
FROM NHCBestTracks1851To2001$
WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST
ORDER BY maxwind_kt DESC, [date], hour_utc) AS comment,
[/quote]

The same as this


 SELECT TOP 1 [NAME], PRESSURE_M, [date], hour_utc, saffirsimp, yearlustor, commnet 
    FROM NHCBestTracks1851To2001$ 
   WHERE NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST 
ORDER BY maxwind_kt DESC, [date], hour_utc

????????

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 14:56:53

That's what I thought.

My suspicion is he has bad data giving the wrong results. They query isn't wrong (well, maybe it's got *a lot* of unneeded code), but the results should be correct.

Wish he'd come back and clue us in.

Sam

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-08 : 15:04:15

Ok sorry for the lateness. Here are the table design and first 25 rows:

1 RECORDNUMB int 4 0
0 NAME char 12 1
0 [DATE] nvarchar 50 1
0 HOUR_UTC smallint 2 1
0 LONGITUDE float 8 1
0 LATITUDE float 8 1
0 YEARLYSTOR smallint 2 1
0 DATABASEST int 4 1
0 MAXWIND_KT smallint 2 1
0 SAFFIRSIMP char 3 1
0 SYSTEMSPEE float 8 1
0 PRESSURE_M smallint 2 1
0 COMMENT char 1 1
0 ENSO char 10 1
0 LASTPOSITI bit 1 0
0 YR smallint 2 1
0 MON smallint 2 1
0 DY smallint 2 1
0 JULIANDY smallint 2 1
0 FIVEYEARPE char 12 1
0 TENYEARPER char 12 1

1 Not Named 5/7/1851 12 -97.6 22.2 1 1 80 SS1 0 0 * 1 1851 7 5 186 1851-1855 1851-1860
2 Not Named 10/7/1851 12 -60 12 2 2 50 TS 0 0 * 1 1851 7 10 191 1851-1855 1851-1860
3 Not Named 16/8/1851 0 -48 13.4 3 3 40 TS 14.9 0 * 0 1851 8 16 228 1851-1855 1851-1860
4 Not Named 16/8/1851 6 -49.5 13.7 3 3 40 TS 14.9 0 * 0 1851 8 16 228 1851-1855 1851-1860
5 Not Named 16/8/1851 12 -51 14 3 3 50 TS 17.9 0 * 0 1851 8 16 228 1851-1855 1851-1860
6 Not Named 16/8/1851 18 -52.8 14.4 3 3 50 TS 18.1 0 * 0 1851 8 16 228 1851-1855 1851-1860
7 Not Named 17/8/1851 0 -54.6 14.9 3 3 60 TS 19 0 * 0 1851 8 17 229 1851-1855 1851-1860
8 Not Named 17/8/1851 6 -56.5 15.4 3 3 60 TS 19.9 0 * 0 1851 8 17 229 1851-1855 1851-1860
9 Not Named 17/8/1851 12 -58.5 15.9 3 3 70 SS1 18.4 0 * 0 1851 8 17 229 1851-1855 1851-1860
10 Not Named 17/8/1851 18 -60.4 16.1 3 3 70 SS1 20.8 0 * 0 1851 8 17 229 1851-1855 1851-1860
11 Not Named 18/8/1851 0 -62.5 16.6 3 3 80 SS1 15.6 0 * 0 1851 8 18 230 1851-1855 1851-1860
12 Not Named 18/8/1851 6 -64.1 16.9 3 3 80 SS1 18.4 0 * 0 1851 8 18 230 1851-1855 1851-1860
13 Not Named 18/8/1851 12 -66 17.2 3 3 90 SS2 15.8 0 * 0 1851 8 18 230 1851-1855 1851-1860
14 Not Named 18/8/1851 18 -67.6 17.6 3 3 90 SS2 16.7 0 * 0 1851 8 18 230 1851-1855 1851-1860
15 Not Named 19/8/1851 0 -69.3 18 3 3 90 SS2 17.6 0 * 0 1851 8 19 231 1851-1855 1851-1860
16 Not Named 19/8/1851 6 -71.1 18.4 3 3 70 SS1 15.1 0 * 0 1851 8 19 231 1851-1855 1851-1860
17 Not Named 19/8/1851 12 -72.6 18.9 3 3 60 TS 16.8 0 * 0 1851 8 19 231 1851-1855 1851-1860
18 Not Named 19/8/1851 18 -74.3 19.4 3 3 60 TS 15.9 0 * 0 1851 8 19 231 1851-1855 1851-1860
19 Not Named 20/8/1851 0 -75.9 19.9 3 3 70 SS1 17.1 0 * 0 1851 8 20 232 1851-1855 1851-1860
20 Not Named 20/8/1851 6 -77.6 20.5 3 3 70 SS1 14.8 0 * 0 1851 8 20 232 1851-1855 1851-1860
21 Not Named 20/8/1851 12 -79 21.2 3 3 70 SS1 14.8 0 * 0 1851 8 20 232 1851-1855 1851-1860
22 Not Named 20/8/1851 18 -80.4 21.9 3 3 70 SS1 11.6 0 * 0 1851 8 20 232 1851-1855 1851-1860
23 Not Named 21/8/1851 0 -81.4 22.6 3 3 60 TS 11.8 0 * 0 1851 8 21 233 1851-1855 1851-1860
24 Not Named 21/8/1851 6 -82.5 23.2 3 3 60 TS 12.3 0 * 0 1851 8 21 233 1851-1855 1851-1860
25 Not Named 21/8/1851 12 -83.6 23.9 3 3 70 SS1 8.1 0 * 0 1851 8 21 233 1851-1855 1851-1860

By hardcoded I ment exactly what you said. The top part (before WHERE (DATABASEST in (223)) AND (COMMENT in ('*'))
GROUP BY DATABASEST) remains fixed

DATABASEST does uniquely identify a storm but not a row.

To further clarify what is going wrong, storm_date, hour_utc (everythign in th correlated selects are not effected by the end where and they should be)

Hope this is clear.

thanks for the help

..dave

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-08 : 15:09:09

Another thing I should mention is that I have to specify RECORDNUMB as criteria as well.. (on the fly) ie>..

select DATABASEST storm_num, max(maxwind_kt) max_wind_speed, datediff(dd, min(convert(datetime, [date],104)), (max(convert(datetime, [date],104)))) duration_days,

(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
(17160,5200,15753,7478,32980,22280,21709,21116,19450,8724,6518,31596,18112,17007,8215,2591,28394,16305,15670,7797,21115,32979,31595,19111,6439,4316,507,26917,16304,13576,8399,2590,25225,24776,8138,21695,19776,15669,11335,7796,5185,1478,30091,28937,17427,14956,6887,3618,2282,26836,25224,24075,8137,5538,30090,21427,7479,5201,26835,15754,11334,6519,6438,25223,14068,8725,3617,19451,18113,5537) and SAFFIRSIMP in ('SS1','SS2','SS3','SS4','SS5','TD','TS') and COMMENT in ('*', 'E') group by DATABASEST ORDER
BY storm_num asc"

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-08 : 18:12:05

I've got to run, but I'll be back later. If no one has solved this by then, I'll have a look.

Click the link "Subscribe to Topic". If anyone posts, you'll get an email. It's easier than checking back.

Meanwhile, if DATABASEST is the storm id, then why all the trouble to get the NAME?

Wouldn't

SELECT TOP 1 [NAME] from NHCBestTracks18.... where N.DATABASEST=S.DATABASEST

Work just fine? Why the ORDER BY clause?

Sam

Edited by - SamC on 07/08/2003 19:37:46

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-08 : 20:08:09

I need the name of the storm as well as the id.

the statment runs an order by every time depending on what column the user wants to order by.

Heres the create table script:

______________________________________________________

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[NHCBestTracks1851To2001$]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NHCBestTracks1851To2001$]
GO

CREATE TABLE [dbo].[NHCBestTracks1851To2001$] (
[RECORDNUMB] [int] NOT NULL ,
[NAME] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DATE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HOUR_UTC] [smallint] NULL ,
[LONGITUDE] [float] NULL ,
[LATITUDE] [float] NULL ,
[YEARLYSTOR] [smallint] NULL ,
[DATABASEST] [int] NULL ,
[MAXWIND_KT] [smallint] NULL ,
[SAFFIRSIMP] [char] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEMSPEE] [float] NULL ,
[PRESSURE_M] [smallint] NULL ,
[COMMENT] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ENSO] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LASTPOSITI] [bit] NOT NULL ,
[YR] [smallint] NULL ,
[MON] [smallint] NULL ,
[DY] [smallint] NULL ,
[JULIANDY] [smallint] NULL ,
[FIVEYEARPE] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TENYEARPER] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

____________________________________________________

Heres another example query (where clause):

select DATABASEST storm_num, max(maxwind_kt) max_wind_speed, datediff(dd, min(convert(datetime, [date],104)), (max(convert(datetime, [date],104)))) duration_days,

(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
(17160,5200,15753,7478,32980,22280,21709,21116,19450,8724,6518,31596,18112,17007,8215,2591,28394,16305,15670,7797,21115,32979,31595,19111,6439,4316,507,26917,16304,13576,8399,2590,25225,24776,8138,21695,19776,15669,11335,7796,5185,1478,30091,28937,17427,14956,6887,3618,2282,26836,25224,24075,8137,5538,30090,21427,7479,5201,26835,15754,11334,6519,6438,25223,14068,8725,3617,19451,18113,5537) and SAFFIRSIMP in ('SS1','SS2','SS3','SS4','SS5','TD','TS') and COMMENT in ('*', 'E') group by DATABASEST ORDER
BY storm_num asc"

____________________________________________________________

This is the logic behind how i developed this sql statement.

This is data broken up by each storm.

The single (non aggregate) values were to be selected at the first occurance (datewise) of the higest windspeed for each storm.

The aggregate values are the ones at the top (besides databasest (the stormnumber) becuase its the one i group by). they are max_wind_speed, durration_days, and number_storm_tracks

I figured I needed all the other fields in their own select statements becuase they are not aggregrate.

Probably not the best way to get what I need but Its the best I could do. (still dont work though)..???

well hope this clarifies a bit

thanks,

-------------------------
dave benoit

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-09 : 00:13:40

quote:
Wouldn't

SELECT TOP 1 [NAME] from NHCBestTracks18.... where N.DATABASEST=S.DATABASEST

Work just fine? Why the ORDER BY clause?

quote:
I need the name of the storm as well as the id.

the statment runs an order by every time depending on what column the user wants to order by.

This doesn't answer the question. For a specific storm_id (column DATABASEST), all rows have the same value for [NAME]. Why are these rows being ordered?

More importantly, how can the value [NAME] be incorrect as you pointed out in your first post?

quote:
the incorrect data are:

storm_name, pressure_M, storm_date, hour_utc, ss_scale, year_storm_num, comment, avg_trans_speed.

All rows for a given DATABASEST have the same value of [NAME] don't they? What incorrect value of [NAME] is returned from your query?

Sam

Edited by - SamC on 07/09/2003 00:14:29

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-09 : 07:51:52

Sorry Sam,

I guess Name comes out okay every time. I just assumed everything in the inner select statements is not gauarenteed to come out right (not effected by the where clause) but of course name would never be effected cause its directly related to the id. I just i just tried to make it clear that the inner selects are not effected by the where clause.
That is the bottom line. that is the problem.

WHen i put an order by at the end it acutally orders by any of the rows. the order by may change on the fly.

_DB

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-09 : 16:24:44

You are changing the ORDER BY to derive different query values. If you'll give me definitions fpr the other queried values beyone [NAME], I'll take a stab at rewriting the query.

What did you think of Brett's suggestion above?

Sam

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-09 : 18:31:35

Are you saying the ORDER BY will actually effect the results IM getting back??? Thats not possible is it? I only want my results sorted. The result I get back are multiple rows and I want to get these rows back sorted by what the user specifies.

I see what bret has done there and yeah it looks normal to be and it obviously will cut things down a bit.

I'll say it again cause Im not sure if its clear. I get back coorect results for

storm_num, max_wind_speed, duration_days and num_tracks.

but [Name], PRESSURE_M, [date], hour_utc, and the other inner selects are not effected by the where clause at the end of the statment although some of them dont need to be probably. but avg_trans_speed definately needs to be effected. I run the sql statment with different values in the end where clause (like comment in ('*'))
and avg trans speed still comes out the same weather i even mention comment or not. and i even get some rows with and E for comment when i should only be getting back *'s in this case.
(this is because the E occurs at the max wind speed for the storm in question).. although this is somewhat right.. i want the comment taken from the maxwindkt within the results that match the criteria in the end where clause

Here is another example of the 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, (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 (32979,30091,26836,14068,21695,15670,14956,6439,3618,16305,8137,7478,19451,5200,8399,5537,31595,26917,26835,21116,21115,17160,15753,7479,6518,28937,24075,18112,8138,5538,5201,15754,6519,30090,28394,8215,7796,2590,25224,22280,21709,18113,13576,8724,31596,19111,507,25223,19776,8725,7797,6887,2282,32980,17007,11334,5185,3617,2591,21427,17427,25225,24776,16304,15669,6438,1478,19450,11335,4316) and SAFFIRSIMP in ('SS1','SS2','SS3','SS4','SS5','TD','TS') and COMMENT in ('*') group by DATABASEST ORDER BY storm_num asc

The last 9 lines here is the where clause. The only other thing that might be in the where clause is [date]. It is this end where clause that might be changed on the fly each time.

A final point I will make here is my attempt at getting this to work.
I thought that if I say "from NHCBestTracks1851To2001$ s " and use the s in each of my inner select statments that the inner selects will only use the data specified in the main where clause.

for example

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-09 : 18:31:35

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-09 : 18:33:41

for example I thought that

(SELECT top 1 round(avg(systemspee),1) FROM NHCBestTracks1851To2001$ where NHCBestTracks1851To2001$.DATABASEST = s.DATABASEST and lastpositi = 0 ) avg_trans_speed

selects the

round(avg(systemspee),1)

from only the data that matches the "end where" criteria

well apparently not and i dont know what to do now and time is running out fast. Well im pretty well already out of time but i still really have to get this done and done corectly.

thanks for the help

dave

dbenoit64
Starting Member

36 Posts

Posted - 2003-07-09 : 20:14:22

also, to add to brets note, Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

SamC
White Water Yakist

3467 Posts

Posted - 2003-07-10 : 00:26:17

Your inner query is restricted only by the storm_num. That's all that is specified in the inner query WHERE, so no, the inner query is not subject to the restrictions of the outer query.

And yes, the ORDER BY will change the values selected in the inner queries when TOP 1 is specified. After all, changing the ordering will change the TOP row won't it?

I'm not sure what [date] is the storm_date? The Min (first) or Max(last)?

Here's an attempt at a simpler form of the query. I'm sure I won't guess all the column calculations correctly the first time but here goes.

Starting with a basic GROUP BY and the result set will have a rowset of the storm_num's you're interested in.

select DATABASEST as storm_num

FROM NHCBestTracks1851To2001$ NHC

WHERE DATABASEST IN (223) AND COMMENT IN ('*')

GROUP BY DATABASEST

Next start adding the remaining results you want calculated

select DATABASEST as storm_num,
MAX(MAXWIND_KT) AS max_wind_speed,
DATEDIFF(dd, MIN([DATE]), MAX([DATE])) AS duration_days,
COUNT(*) AS num_tracks,
MAX([NAME]) AS storm_name, -- MAX or MIN will do
MIN(PRESSURE_M) as PRESSURE_M, -- Minimum Storm Pressure ?
MIN([DATE]) AS storm_date, -- First date of Storm ?
MIN(hour_utc) as hour_utc, -- I have no idea what this is
MAX(maxwind_kt) as max_wind, -- Throw this in for fun
-- I'll try to include saffirsimp, yearlystor, comment if you'll tell me what it is you are trying to pull out of the query. Looks like the Top 1 sorted by wind speed?
AVG(systemspee) AS avg_trans_speed -- Here, I'm lost on how an average can be calculated when where lastpositi=0 may restrict it to a single value?

FROM NHCBestTracks1851To2001$ NHC

WHERE DATABASEST IN (223) AND COMMENT IN ('*')

GROUP BY DATABASEST

If you'll post the definitions of the column results in the query, the calculations I've tried to guess at could be corrected.

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources