[SQL Server] Advancing Knowledge of Subqueries

---

Advancing Knowledge of Subqueries Posted: 03 Sep 2013 03:05 AM PDT I am currently reading through Itzik-Ben-Gans [i]Microsoft SQL 2008 T-SQL Fundamentals book[/i] and I've made sense of it so far. I'm completely lost in subqueries. I get the general basis of this information, but understanding the logic behind this type of code eludes me: [code] Select orderid, orderdate, empid, custid, (Select MAX(O2.orderid) FROM Sales.Orders AS O2 WHERE O2.orderid < O1.orderid) AS Prevorderid FROM SALES.ORDERS AS O1;[/code]This will display the order id that goes before the current order id in the new column prevorderid. Why? If someone could produce some type of pseudo code or explain to me why this subquery functions the way it does I would be very appreciative. Thank you very much.

Please help on these error messages Posted: 02 Sep 2013 11:01 PM PDT Please help, I am getting these error messages.Msg 156, Level 15, State 1, Line 38Incorrect syntax near the keyword 'AS'.Msg 156, Level 15, State 1, Line 45Incorrect syntax near the keyword 'AS'.Msg 156, Level 15, State 1, Line 51Incorrect syntax near the keyword 'AS'.The query is below:DECLARE @Results TABLE( studyCode nvarchar(40), StudyDirector nvarchar(244), activityCode nvarchar(40), PhaseType nvarchar(244), ActivityOwner nvarchar(244),TQSDComment nvarchar(2000),TQPIComment nvarchar(2000));INSERT @Results (studyCode,StudyDirector,activityCode,ActivityOwner)SELECT S.studyCode AS studyCode, SOP.operatorName AS StudyDirector, A.activityCode AS activityCode, AOP.operatorName AS ActivityOwner FROM Activities A INNER JOIN TypesOfActivities TOA ON A.typeOfActivityIncId = TOA.typeOfActivityIncId AND A.typeOfActivitySqlId = TOA.typeOfActivitySqlId AND TOA.isDeleted=0x0 INNER JOIN ActivitiesCategories AC ON A.activityCategoryIncId = AC.activityCategoryIncId AND A.activityCategorySqlId = AC.activityCategorySqlId AND AC.isDeleted=0x0 INNER JOIN Studies S ON A.studyIncId = S.studyIncId AND A.studySqlId = S.studySqlId AND S.isDeleted=0x0 INNER JOIN TypesOfStudies TOS ON S.typeOfStudyIncId = TOS.typeOfStudyIncId AND S.typeOfStudySqlId = TOS.typeOfStudySqlId AND TOS.isDeleted=0x0 --Filter on AF with extractName 'TrialEvaluation' AND the AFValue 'major issue : trial not valid/cancelled ' (But use the AFV code instead of the name) LEFT JOIN ActivitiesAdditionalFieldsValues AS AAFV WITH(NOLOCK) ON A.activityIncId=AAFV.activityIncId AND A.activitySqlId=AAFV.activitySqlId AND AAFV.isDeleted=0x0 INNER JOIN ActivitiesAdditionalFields AS AAF WITH(NOLOCK) ON AAFV.activityAdditionalFieldIncId=AAF.activityAdditionalFieldIncId AND AAFV.activityAdditionalFieldValueSqlId=AAF.activityAdditionalFieldSqlId AND AAF.isDeleted=0x0 AND AAF.extractName = 'TrialEvaluation' INNER JOIN CboValues CBOV ON AAFV.cboRecordIncId = CBOV.cboValueIncId AND AAFV.cboRecordSqlId = CBOV.cboValueSqlId AND CBOV.isDeleted=0x0 AND CBOV.cboValueCode = 'EAS-3003' -->'major issue : trial not valid/cancelled ' LEFT JOIN Operators AS AOP ON A.todoBySqlId = AOP.operatorSqlId AND A.todoByIncId = AOP.operatorIncId LEFT JOIN Operators AS SOP ON S.directedBySqlId = SOP.operatorSqlId AND S.directedByIncId = SOP.operatorCategoryIncId WHERE A.isDeleted=0x0 AND TOS.typeOfStudyCode = 'EAS-01' AND TOA.typeOfActivityCode = 'EAS-1' AND AC.activityCategoryCode = 'EAS-1F' ----AND (A.activityStartDate < DateAdd(DAY,1,@ToDate) AND A.activityEndDate >= @FromDate) ORDER BY S.studyCode, A.activityCode UPDATE @Results SET PhaseType = CASE ISNULL(CATCA.activityCategoryName, '') WHEN '' THEN TOCA.typeOfActivityName ELSE TOCA.typeOfActivityName + ' ; ' + CATCA.activityCategoryName END AS PhaseType FROM Activities AS CA LEFT JOIN ActivitiesCategories AS CATCA ON CATCA.activityCategorySqlId=CA.activityCategorySqlId AND CATCA.activityCategoryIncId=CA.activityCategoryIncId AND CATCA.isDeleted=0x0 LEFT JOIN TypesOfActivities AS TOCA ON CA.typeOfActivitySqlId=TOCA.typeOfActivitySqlId AND CA.typeOfActivityIncId=TOCA.typeOfActivityIncId AND TOCA.isDeleted=0x0 ON A.activitySqlId=CA.fatherActivitySqlId AND A.activityIncId=CA.fatherActivityIncId AND CA.isDeleted=0x0 AND TOCA.typeOfActivityName Like '%Running%' UPDATE @Results SET TQSDComment = SDAAFV.txtValue AS TQSDComment FROM ActivitiesAdditionalFieldsValues AS SDAAFV LEFT JOIN ActivitiesAdditionalFields AS SDAAF ON SDAAFV.activityAdditionalFieldIncId=SDAAF.activityAdditionalFieldIncId AND SDAAFV.activityAdditionalFieldValueSqlId=SDAAF.activityAdditionalFieldSqlId AND SDAAF.isDeleted=0x0 ON A.activityIncId=SDAAFV.activityIncId AND A.activitySqlId=SDAAFV.activitySqlId AND SDAAFV.isDeleted=0x0 AND SDAAF.extractName = 'TQSDComment' UPDATE @Results SET TQSDComment = PIAAFV.txtValue AS TQPIComment FROM ActivitiesAdditionalFieldsValues AS PIAAFV LEFT JOIN ActivitiesAdditionalFields AS PIAAF ON PIAAFV.activityAdditionalFieldIncId=PIAAF.activityAdditionalFieldIncId AND PIAAFV.activityAdditionalFieldValueSqlId=PIAAF.activityAdditionalFieldSqlId AND PIAAF.isDeleted=0x0 ON A.activityIncId=PIAAFV.activityIncId AND A.activitySqlId=PIAAFV.activitySqlId AND PIAAFV.isDeleted=0x0 AND PIAAF.extractName = 'TQPIComment'SELECT * FROM @Results;

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / SQL Server Newbies To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610