July 13, 2008 at 12:58 am
How do I display sample records (let's say 3 items ordered) on the last order for each buyer?
Thank you!
July 13, 2008 at 5:21 am
If you can post the code that would help a lot in providing an answer. Thanks in advance.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 13, 2008 at 12:28 pm
Please read the following article:
Best Practices: http://www.sqlservercentral.com/articles/Best+Practices/61537/
If you follow the above, someone here will be able to help you solve your problem - probably very quickly.
Off the top of my head, are you looking for:
SELECT TOP(3)
column1
,column2
,...
FROM mytable
WHERE myconditions
ORDER BY someorder;
Somehow, I think your problem is more complex than the above - but I am not sure.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2008 at 10:29 am
Without knowing your table structure the issue is difficult to assess but here is a possible solution you can work with to gather TOP x items for each buyer based on the last order id. I could provide a more accurate solution if I knew the schema.
SELECT TOP(3)
a.col1,
a.col2...
FROM
mytable a
WHERE
a.orderid = (select max(orderid) from mytable b where a.buyer = b.buyer)
GROUP BY
a.buyer
July 14, 2008 at 1:10 pm
First, this post is part of a cross-posting. Please don't post a topic twice. As to what you seek, it would appear you may just need a simple join using a TOP query, along the lines of the following:
CREATE TABLE BUYERS (buyer_id bigint, order_id bigint, item_id bigint)
INSERT INTO BUYERS VALUES (1, 32, 8)
INSERT INTO BUYERS VALUES (1, 31, 2)
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 16, 5)
INSERT INTO BUYERS VALUES (2, 32, 4)
INSERT INTO BUYERS VALUES (2, 14, 7)
INSERT INTO BUYERS VALUES (2, 9, 8)
INSERT INTO BUYERS VALUES (2, 16, 2)
INSERT INTO BUYERS VALUES (2, 18, 5)
INSERT INTO BUYERS VALUES (2, 24, 1)
;WITH ORDERS AS (
SELECT MIN(order_id) AS order_id, buyer_id
FROM BUYERS
GROUP BY buyer_id
)
SELECT A.order_id, A.buyer_id, B.item_id
FROM ORDERS AS A LEFT OUTER JOIN (
SELECT TOP 5 C.item_id, C.buyer_id
FROM BUYERS AS C
WHERE C.buyer_id=A.buyer_id
ORDER BY C.item_id
) AS B ON A.buyer_id=B.buyer_id
ORDER BY A.order_id, A.buyer_id
I can't test this, but you should be able to adapt it as needed.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 14, 2008 at 9:26 pm
I'm going to assume that items are in a separate table to order.
with BuyerOrder as
(
select
b.order_id, b.buyer_id, o.order_desc, i.item_desc, i.item_id,
rank() over (partition by b.buyer_id order by b.order_id desc) as OrderRank,
rank() over (partition by b.buyer_id, b.order_id order by i.item_id) as ItemRank
from buyer b
join [order] o
on b.order_id = o.order_id
join item i
on o.order_id = i.order_id
)
select order_id, item_id, buyer_id, order_desc, item_desc
from BuyerOrder
where OrderRank = 1 and ItemRank <= 3
July 14, 2008 at 9:43 pm
Thank you again, John!
I'll test it tomorrow at work.
July 14, 2008 at 9:49 pm
In another thread you mentioned that you are using SQL2000, so this code won't work for you, sorry. As this is a SQL2005 forum I assumed you were using that.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply