August 16, 2011 at 5:17 pm
I tried to read and understand some other person created a stored procedure, in the join subquery, after the subquery name latest,see the underlined, what does ((StudentId, RequestNbr) mean here ? I never saw the syntax before, thanks
SELECT @RequestDate = Request.RequestDate,
@RequestNbr = Request.RequestNbr
FROM Request
JOIN (SELECT StudentId, max(RequestNbr)
FROM Request
WHERE RequestTypeId = @RequestTypeId
AND StudentId = @StudentId
AND CompletionDate IS NULL
AND SchoolYear= @SchoolYear
GROUP BY StudentId) Latest (StudentId, RequestNbr)ON Request.StudentId = Latest.StudentId
AND Request.RequestNbr = Latest.RequestNbr
August 16, 2011 at 5:53 pm
It is not a common syntax, but it is listing the column names for the virtual table created by that subquery.
Here is syntax that is easier to read.
DECLARE @studentID INT = 1
,@RequestDate DATETIME = GETDATE()
,@RequestNBR INT = 2
,@Schoolyear INT = 2011
,@RequestTypeID INT = 1
;
WITH Request AS (
SELECT RequestNbr = 2
, RequestDate = @RequestDate
, studentid = 1
, schoolyear = 2011
, requesttypeid = 1
,completiondate = null)
SELECT *
--@RequestDate = Request.RequestDate,
--@RequestNbr = Request.RequestNbr
FROM Request
JOIN (SELECT StudentId, max(RequestNbr)
FROM Request
WHERE RequestTypeId = @RequestTypeId
AND StudentId = @StudentId
AND CompletionDate IS NULL
AND SchoolYear= @SchoolYear
GROUP BY StudentId) Latest (StudentId, RequestNbr) --columns of subquery table
ON Request.StudentId = Latest.StudentId
AND Request.RequestNbr = Latest.RequestNbr
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 16, 2011 at 9:00 pm
Still cannot get it.Any documentation online about the syntax?
I know CTE, but this seems not it.
August 16, 2011 at 9:40 pm
It is actually very similar to the construct for a cte. A CTE is in essence the new way of doing a derived table.
Break down the query like this
SELECT SOMecolumns
FROM yourtable
JOIN (...
guts OF derived TABLE
) AS derivedtablealias (columnlist)
In your case, you are selecting from Request and then joining to a subquery/derived table. The derived table is everything between the parenthesis after the join statement. After you define your derived table/subquery, then you give it an alias (Latest is the name used in your case) and then you define a column list after that alias.
This is rarely used, but you can look at the following articles for reference.
Derived Tables and Column Alias list http://msdn.microsoft.com/en-us/library/ms177634.aspx
And a clearer example in Sybase Documentation (same syntax as SQL Server): http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug411.htm
Same optional syntax is available in CTE (the derived table successor): http://msdn.microsoft.com/en-us/library/ms190766.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 6:56 am
I've never come across this syntax in 10 years of T-SQL programing, but its good to see it, and the explination for it.
I think 98% of people would written the original query as
SELECT @RequestDate = Request.RequestDate,
@RequestNbr = Request.RequestNbr
FROM Request
JOIN (SELECT StudentId
, max(RequestNbr) AS RequestNbr
FROM Request
WHERE RequestTypeId = @RequestTypeId
AND StudentId = @StudentId
AND CompletionDate IS NULL
AND SchoolYear = @SchoolYear
GROUP BY StudentId) Latest ON Request.StudentId = Latest.StudentId
AND Request.RequestNbr = Latest.RequestNbr
With the column Aliased in the the subquery.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
August 17, 2011 at 7:05 am
Jason-299789 (8/17/2011)
I've never come across this syntax in 10 years of T-SQL programing, but its good to see it, and the explination for it.I think 98% of people would written the original query as
...
It would not be wrong to say 99.99% of people in modern time :-D.
I've seen it few times but it was long ago in Sybase.
May be some one did read too many of J.Celko books at night :hehe:
August 17, 2011 at 9:08 am
Thanks, it's good information, and it's helpful to know this is rarely used, and the other favoribale option of doing that.
August 17, 2011 at 9:12 am
I have seen it many times more often in a CTE than the older Derived table. I think this syntax has some usefulness when thinking through it. I probably wouldn't use it very often, but it is good to know.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 3:25 pm
Never seen that either. Interesting indeed.
August 17, 2011 at 3:55 pm
It won't work until 8/18, but here is a link for a post I wrote on the topic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 17, 2011 at 3:58 pm
Thanks, the link is not working now, but I will check back later on it.
August 17, 2011 at 8:51 pm
SQLRNNR (8/17/2011)
I have seen it many times more often in a CTE than the older Derived table. I think this syntax has some usefulness when thinking through it. I probably wouldn't use it very often, but it is good to know.
Its a very useful syntax. I use it often. I was asked to teach a newbie some basics about Aggregation and i had this syntax help me out ( and eventually him) on better readability.
Here is a small snippet used in my KT.
Select a , sum (b) , sum( c * b)
from
( select 1 ,10 , 2
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
) T ( a, b , c )
Group by a
see this makes the data easily readable.
Of course we can write it other ways, as in
Select a , sum (b) , sum( c * b)
from
( select 1 AS a ,10 AS b , 2 AS c
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
) T
Group by a
or in a CTE as
; with cte ( a , b , c) as
( select 1 ,10 , 2
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
)
Select a , sum (b) , sum( c * b)
from cte
Group by a
August 17, 2011 at 9:00 pm
ColdCoffee (8/17/2011)
SQLRNNR (8/17/2011)
I have seen it many times more often in a CTE than the older Derived table. I think this syntax has some usefulness when thinking through it. I probably wouldn't use it very often, but it is good to know.Its a very useful syntax. I use it often. I was asked to teach a newbie some basics about Aggregation and i had this syntax help me out ( and eventually him) on better readability.
Here is a small snippet used in my KT.
Select a , sum (b) , sum( c * b)from
( select 1 ,10 , 2
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
) T ( a, b , c )
Group by a
see this makes the data easily readable.
Of course we can write it other ways, as in
Select a , sum (b) , sum( c * b)
from
( select 1 AS a ,10 AS b , 2 AS c
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
) T
Group by a
or in a CTE as
; with cte ( a , b , c) as( select 1 ,10 , 2
union all select 1 ,10 , 4
union all select 1 ,10 , 6
union all select 2 ,1 , 5
union all select 2 ,5 , 10
)
Select a , sum (b) , sum( c * b)
from cte
Group by a
Great stuff. Thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply