Re: Query/Table term confusion
From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 03/23/04
- Previous message: JasonCook: "Query/Table term confusion"
- In reply to: JasonCook: "Query/Table term confusion"
- Next in thread: JasonCook: "Re: Query/Table term confusion"
- Reply: JasonCook: "Re: Query/Table term confusion"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 24 Mar 2004 01:39:21 +0530
hi jason,
--lets assume freight as an order amount on the northwind sample database.
use northwind
go
select OrderId, OrderDate , O.freight
,(select sum(freight) from Orders
where OrderID <= O.OrderID)
'Running Total'
from Orders o
order by orderid
--select * from orders
>>what is the purpose of the "O"
The O is an alias given to the outer table of the query. Since you must have
understood that
the complete query consists of subquery as well, so to distinguish between
table inside
the subquery and the outer query alias is used.
The concept of subquery is that all the rows returned by the subquery will
be compared with the
each row of outer table.
for example lets assume table consists of only 3 rows.
ordid freight run_total
-------------------------
10248 32.3800 32.3800
10249 11.6100 43.9900
10250 65.8300 109.8200
in above case subquery will compare all the three rows with the outer
table.So the loop
will have logical construct as follows.
is 10248 of inner table <= 10248 of outer table (Y) so amount will be 32.38
is 10249 of inner table <= 10248 of outer table (N) so amount will be 00.00
is 10250 of inner table <= 10248 of outer table (N) so amount will be 00.00
------------
(running total) sum for the 1st row will be: 32.00
loop construct for 2nd row will be as follows.
is 10248 of inner table <= 10249 of outer table (Y) so amount will be 32.38
is 10249 of inner table <= 10249 of outer table (Y) so amount will be 11.61
is 10250 of inner table <= 10249 of outer table (N) so amount will be 00.00
------------
(running total) sum for the 2ndt row will be: 43.99
loop construct for 3rd row will be as follows.
is 10248 of inner table <= 10250 of outer table (Y) so amount will be 32.38
is 10249 of inner table <= 10250 of outer table (Y) so amount will be 11.61
is 10250 of inner table <= 10250 of outer table (Y) so amount will be 65.83
------------
(running total) sum for the 3rd row will be: 109.82
HTH
-- Vishal Parkar vgparkar@yahoo.co.in
- Previous message: JasonCook: "Query/Table term confusion"
- In reply to: JasonCook: "Query/Table term confusion"
- Next in thread: JasonCook: "Re: Query/Table term confusion"
- Reply: JasonCook: "Re: Query/Table term confusion"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
