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

Hi

I've never seen a query constructed like this, if you post your tables

and data I will have a go as i'm sure you've made it more complicated

than you need to. The select top 1 looks incorrect in all the sub-querys

straight away.

Quite often the use of brackets affects sql statements.

dregs2

Actually brackets are used when you name a column with a reserved word, like Date, in the RDBMS.
========================================
Buh Bye!

Cheers,
Me

it might look complicated but its not really.
I just need the where statment at the end of it to effect my inner selects. I hate to put the where in there 8 (or whatever) times but I think i will have to revert to that.


just a note on how to do that would be fine :)

_________________________________________________________________________________


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

_____________________________________________________________________