Re: Very Tricky SELECT statement
From: Eric Sabine (mopar41_at____ho_y_tmail.ScPoAmM)
Date: 04/05/04
- Next message: Zach Wells: "Re: WHERE clause question"
- Previous message: Vlad: "Re: Subselect question"
- In reply to: Stewart Saathoff: "Very Tricky SELECT statement"
- Next in thread: Zach Wells: "Re: Very Tricky SELECT statement"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 5 Apr 2004 16:11:19 -0400
Here's an example of how you would do it with a correlated subquery. It can
be adapted to your table schema.
use northwind
go
select a.*,
(select top 1 productid
from [Order Details] b
where a.orderid = b.orderid
order by b.productid desc) as productId
from orders a
hth
Eric
"Stewart Saathoff" <stewarts@complete-networking.com> wrote in message
news:eqnpni0GEHA.1264@TK2MSFTNGP10.phx.gbl...
> Hey Everyone,
>
> I have a very tricky select statement that I need help with.
>
> I have a table that contains orders and order details.
>
> I want to retrieve the last detail record from every unique order that was
> placed. The last record is known by the exact date and time that it was
> ordered. The order details table is similar to this:
>
> orderdetid PK int
> orderid FK int
> Item String
> orderdate date/time
>
> I know how to retrieve the last records in a database using the TOP
command.
> but I am not sure how to retrieve every top record from say 5000 orders...
>
>
- Next message: Zach Wells: "Re: WHERE clause question"
- Previous message: Vlad: "Re: Subselect question"
- In reply to: Stewart Saathoff: "Very Tricky SELECT statement"
- Next in thread: Zach Wells: "Re: Very Tricky SELECT statement"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
