Query Concept  
Author Message
Fitness





PostPosted: Fri Sep 02 09:36:18 CDT 2005 Top

SQL Server Developer >> Query Concept

Hi,
I would like to use Northwind as an example.

I am trying to use the Orders table and why the following query only returns
one record for each customer if the outer query pass info into the inner
query since each customer has more than one records in the Orders table.

e.g. customerid "ALFKI" has six records in Orders table, but after the query
run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
into the inner query six times... it is supposed to show six identitcal
reocrds for "ALFKI", but not, why???

select a.* from orders a where a.orderdate = (select max(orderdate) from
orders b where a.customerid = b.customerid)
order by a.customerid

Thanks

Edmund

SQL Server140  
 
 
David





PostPosted: Fri Sep 02 09:36:18 CDT 2005 Top

SQL Server Developer >> Query Concept In principle the subquery is evaluated for each row but it will always
return the same value for any given customerid. If there is only one
value that corresponds to the maximum date for "ALKFI" then you will
only get one row returned for that customer.

Please show us exactly what result you want then maybe someone can help
with a solution.

--
David Portas
SQL Server MVP
--

 
 
Ed





PostPosted: Fri Sep 02 09:55:19 CDT 2005 Top

SQL Server Developer >> Query Concept Actually, i am just wondering why I don't get six records back for customer
"ALFKI" but just one since I guess the outer query is passing into the inner
query for six times even I may get six identical records...



> In principle the subquery is evaluated for each row but it will always
> return the same value for any given customerid. If there is only one
> value that corresponds to the maximum date for "ALKFI" then you will
> only get one row returned for that customer.
>
> Please show us exactly what result you want then maybe someone can help
> with a solution.
>
> --
> David Portas
> SQL Server MVP
> --
>
>
 
 
Perayu





PostPosted: Fri Sep 02 09:59:47 CDT 2005 Top

SQL Server Developer >> Query Concept Because the subquery will return only one record, which has the last
orderdate, for each CustomerID.

Perayu



> Hi,
> I would like to use Northwind as an example.
>
> I am trying to use the Orders table and why the following query only
> returns
> one record for each customer if the outer query pass info into the inner
> query since each customer has more than one records in the Orders table.
>
> e.g. customerid "ALFKI" has six records in Orders table, but after the
> query
> run, it only returns one record for "ALFKI". I assume "ALFKI" should pass
> into the inner query six times... it is supposed to show six identitcal
> reocrds for "ALFKI", but not, why???
>
> select a.* from orders a where a.orderdate = (select max(orderdate) from
> orders b where a.customerid = b.customerid)
> order by a.customerid
>
> Thanks
>
> Edmund
>
>
>
>
>
>
>


 
 
David





PostPosted: Fri Sep 02 10:18:24 CDT 2005 Top

SQL Server Developer >> Query Concept Maybe the following example will make it clearer:

SELECT a.customerid, a.orderdate,
(SELECT MAX(orderdate)
FROM orders b
WHERE a.customerid = b.customerid) AS subquery_result
FROM orders a
WHERE a.customerid = 'ALFKI' ;

Here I've just moved the subquery into the select list so that you can
see the result it returns for each row - the same value each time. You
can also see that there is only ONE row that matches the value returned
by the subquery, therefore that's the only row that satisfies the
condition in your WHERE clause.

--
David Portas
SQL Server MVP
--

 
 
AlejandroMesa





PostPosted: Fri Sep 02 10:23:05 CDT 2005 Top

SQL Server Developer >> Query Concept Ed,

> Actually, i am just wondering why I don't get six records back for customer
> "ALFKI" but just one since I guess the outer query is passing into the inner
> query for six times even I may get six identical records...

From all alfki's orders, just one is the latest. Eventhough the outer query
is passing value six times, the value is the same (customerid) and the value
returned is always the same (max(orderdate)).




from orders
where customerid = 'alfki'



select *
from orders

go


AMB



> Actually, i am just wondering why I don't get six records back for customer
> "ALFKI" but just one since I guess the outer query is passing into the inner
> query for six times even I may get six identical records...
>

>
> > In principle the subquery is evaluated for each row but it will always
> > return the same value for any given customerid. If there is only one
> > value that corresponds to the maximum date for "ALKFI" then you will
> > only get one row returned for that customer.
> >
> > Please show us exactly what result you want then maybe someone can help
> > with a solution.
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >