×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

coorelated sub query with group by

coorelated sub query with group by

coorelated sub query with group by

(OP)
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






RE: coorelated sub query with group by

Unfortunately I think you have to have the WHERE clause in each of the statements based on the way you've structured it.  Perhaps you could make a nested query that filters the table based on the overall WHERE conditions first, then perform the rest of your query on the resulting dataset?

Hope this helps!

BML

RE: coorelated sub query with group by

You had said the nested selects are 'hard-coded', but you have power to change the code, correct?  I believe there's a fair amount you can do to make this code easier to handle... If you can change the code itself, I think we could make your life much easier.  Is it safe to assume this is pulling from an MS-SQL database?  

If you have some sample data to play w/, this looks like it could be fun to toy around w/.  Off the top of my head (though not tested), you should be able to collapse the majority of your selects into a single select since all but the one uses the same where clause:...so instead of:

select id, max(this), min(that),
(select this from this where this.id = b.id) as thisother,
(select that from this where this.id = b.id) as thatother,
(select third from this where this.id = b.id) as thisthird
from maintable b
where etc
group by this, that

you could collapse the sub-selects like this:

select id, max(this), min(that), thisother, thatother, thisthird
from (select this 'thisother', that 'thatother',
third 'thisthird' from this where this) a,
    maintable b
where a.id = b.id
and etc
group by this, that

I don't think it will be that bad to nail something down that would be scalable if you have superuser access.  I'd imagine this page is on an internal network somewhere, but on the odd chance it's accessible externally, I'd love to see the page to get a feel for what I'm dealing with.  (the answer to x+y is much easier when x & y are defined) :)

RE: coorelated sub query with group by

(OP)
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

dave benoit

RE: coorelated sub query with group by

I need to take off at the moment, but I'll check back and try to elaborate later.  

Notice the location of the subqueries in the example from earlier.  The subquery set is after the FROM, not part of the SELECT. You can choose as many columns as you want when done this way.  You're basically creating a virtual table to join to the main table (in my example called 'a').  Try playing a bit...

Second note:  The 'lastpositi = 0' portion in your last subquery, unless that can be included in the WHERE clause of the main statement, that may need to stay a subquery (though I don't see this being a real problem if you're using asp or such to generate the script) [hate not having data in front of me to play w/].

In a rush, so I hope that makes some sense...

Good luck boss.

Dan

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close