Query Concept

2 Sep 2005 2:27 PM

Ed

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

2 Sep 2005 2:36 PM

David Portas

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
--

2 Sep 2005 2:55 PM

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...

Show quote

"David Portas" wrote:

> 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
> --
>
>

2 Sep 2005 3:18 PM

David Portas

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
--

2 Sep 2005 3:23 PM

Alejandro Mesa

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)).

declare @d datetime

select @d = max(orderdate)
from orders
where customerid = 'alfki'

print @d

select *
from orders
where customerid = 'alfki' and orderdate = @d
go

AMB

Show quote

"Ed" wrote:

> 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...
>
> "David Portas" wrote:
>
> > 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
> > --
> >
> >

2 Sep 2005 2:59 PM

Perayu

Because the subquery will return only one record, which has the last
orderdate, for each CustomerID.

Perayu

Show quote

"Ed" <E*@discussions.microsoft.com> wrote in message
news:D85ECABE-E0C5-431F-9948-B329086B1A31@microsoft.com...
> 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
>
>
>
>
>
>
>