• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

NESTED SQL

Hi, We have this sql statement

UPDATE BRenewals
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate  , ServiceLevel = a.ServiceLevel
FROM BRenewals b INNER JOIN
    (SELECT DateIssued, MAX(RenewalDate) AS RenewalDate, ServiceLevel, AlarmRef
FROM AService
GROUP BY AlarmRef, DateIssued, ServiceLevel) a ON b.AlarmRef = a.AlarmRef

- We are trying to insert the max value into a table BRenewals where AlarmRef are equal for fields in question
- Focus is on the nested SELECT statement!
- It works perfectly when we remove all fields other than AlarmRef and RenewalDate in the select and the GROUP BY (i.e. it shows and inserts into BRenewals the MAX value)
- However, when uisng all the fields specified in SQL above, it basically returns records if two records exist (i.e. it doesn't doesn't show the MAX value only)

Can anybody help with this. It's so near, but we need to specify all the fields in the SELECT statement so the update has reference to them! Thought about using a temporary table to insert the SELECT statement into and then reference that temp tabke, but would rather avoid another temp table if we could!
0
StuartK
Asked:
StuartK
  • 4
  • 3
  • 2
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
Hi.

Are you wanting the DateIssued and ServiceLevel corresponding to the row with the MAX(RenewalDate)? If so, ROW_NUMBER() OVER(PARTITION BY AlarmRef ORDER BY RenewalDate DESC) RN may serve you better. That will rank all the rows within an AlarmRef with the MAX(RenewDate) row being RN = 1.

If on the other hand, all three fields must equal, then you have to ensure that all three fields are compared in the ON clause as part of the JOIN.

As an aside, you should use the alias "b" as the target of the update, i.e., UPDATE b ...
0
 
Kevin CrossChief Technology OfficerCommented:
Here is an article I wrote on ranking, if you are unfamiliar with the concept:
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_1555-Analytical-SQL-Where-do-you-rank.html
0
 
HainKurtSr. System AnalystCommented:
if you are joining only with alarmref you should group by that column only

so try this:

UPDATE BRenewals
SET DateIssued = a.DateIssued , RenewalDate = a.RenewalDate  , ServiceLevel = a.ServiceLevel 
FROM BRenewals b  INNER JOIN
(
select * from (
SELECT *, row_number() over (partition by AlarmRef order by RenewalDate desc) rn from AService
) x where rn=1
) a ON b.AlarmRef = a.AlarmRef

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
oops, I guess my post is the implementation of what mwvisa1 explains :)

by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
0
 
Kevin CrossChief Technology OfficerCommented:
I would do it more like this to avoid double derived queries plus to clear up the ambiguity in columns in SET; however, the above is pretty much what I was suggesting using ROW_NUMBER().

UPDATE b
SET b.DateIssued = a.DateIssued 
  , b.RenewalDate = a.RenewalDate  
  , b.ServiceLevel = a.ServiceLevel 
FROM BRenewals b 
INNER JOIN (
   SELECT DateIssued, RenewalDate, ServiceLevel, AlarmRef
        , ROW_NUMBER() OVER(PARTITION BY AlarmRef ORDER BY RenewalDate DESC) RN
   FROM AService
) a ON b.AlarmRef = a.AlarmRef AND a.RN = 1
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
@HainKurt, regarding:
by the way "UPDATE b ..." or "UPDATE BRenewals..." both valid...
Since it is valid syntax to do UPDATE tbl1 ... FROM tbl2 ... without specifying tbl1 in the FROM clause, the UPDATE not using the alias is a bit untrustworthy because BRenewals and b are two different tables at that point. Therefore, my suggestion holds at using "b" if aliased as such in the FROM. ;-)
0
 
HainKurtSr. System AnalystCommented:
captcha :)
0
 
StuartKAuthor Commented:
You are all genius's!!!
0
 
StuartKAuthor Commented:
My apolgies, you are both genius's, and I thnka you so much! I've learnt a little more thanks to that! One day I might be gods like you too!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now