July 12, 2008 at 5:33 pm
Thanks everyone who responded to my previous question!
Turned out that I misunderstood the task and used a totally different solution for it.
Now new task.
There are two tables: buyers and orders.
order_id field is a primary key in orders table and foreign key in buyers table.
Buyer_id and order_id in buyers table relationship is one-to-many.
There is another field in orders table - order_desc.
What I need:
select first order for each buyer, also include order description.
I wrote this:
select min(b.order_id), b.buyer_id, o.order_desc
from buyer b
join order o
on b.order_id = o.order_id
group by b.buyer_id
Without order_desc it works fine and returns the correct result.
Once I include order_desc into the select statement, it naturally starts complaining about order_desc not participating in either aggregate function or group by clause and therefore not being legitimate on the select list.
If I add it to the group by clause, it of course gives me a wrong result.
How do I trick it?
Thank you!
July 12, 2008 at 6:40 pm
You can do this with a CTE or Derived Table. Here is the CTE version.
With BuyerOrder
AS
(
select min(b.order_id) order_id, b.buyer_id
from buyer b
join [order] o
on b.order_id = o.order_id
group by b.buyer_id
)
Select A.order_id, A.buyer_id, o.order_desc
FROM BuyerOrder A JOIN
[order] o ON
A.order_id = o.order_id
July 12, 2008 at 6:44 pm
Here is the Derived Table version. This will also work with 2000 if needed.
Select A.order_id, A.buyer_id, o.order_desc
FROM (select min(b.order_id) order_id, b.buyer_id
from buyer b
join [order] o
on b.order_id = o.order_id
group by b.buyer_id) A JOIN
[order] o ON
A.order_id = o.order_id
July 12, 2008 at 8:40 pm
Thank you! I'll try it.
July 12, 2008 at 9:34 pm
It worked!
Thank you again!
July 14, 2008 at 8:55 pm
Assuming this is SQL2005 the following query will work.
with BuyerOrder as
(
select
b.order_id, b.buyer_id, o.order_desc,
row_number() over (partition by b.buyer_id order by b.order_id) as RowNum
from buyer b
join [order] o
on b.order_id = o.order_id
)
select order_id, buyer_id, order_desc
from BuyerOrder
where RowNum = 1
July 14, 2008 at 9:42 pm
Thank you!
I'll try this one, too.
But I wonder if CTE is SQL 2000 compatible. My database is on a SQL 2000 server.
July 14, 2008 at 9:47 pm
CTEs and ranking functions are not compatible with SQL2000. You will have to use the derived table approched mentioned earlier.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply