July 13, 2008 at 4:05 pm
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)
The task is to display sample 3 records of the first (minimum) order for each buyer.
I managed to get just one record for the first order for each buyer by the below code:
Select A.order_id, A.buyer_id
FROM (select min(order_id) order_id, buyer_id
from BUYERS
group by buyer_id) A
Now I'm struggling trying to display 3 records (three items) for each of these orders.
Thank you!
July 13, 2008 at 5:15 pm
I think this is what you are looking for.
Select Buyer_ID,Order_ID
FROM (
select order_id, buyer_id,
RANK() OVER (partition BY buyer_id Order by order_id) Rnk
from BUYERS
GROUP BY order_id, buyer_id) A
WHERE Rnk < 4
July 13, 2008 at 5:25 pm
levsha_z (7/13/2008)
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)
The task is to display sample 3 records of the first (minimum) order for each buyer.
I managed to get just one record for the first order for each buyer by the below code:
Select A.order_id, A.buyer_id
FROM (select min(order_id) order_id, buyer_id
from BUYERS
group by buyer_id) A
Now I'm struggling trying to display 3 records (three items) for each of these orders.
Thank you!
How about the following:
;With cte (buyerid, orderid, rn)
As (Select buyer_id
,order_id
,row_number() over(Partition By buyer_id Order By order_id)
From (Select Distinct
buyer_id
,order_id
From @Buyers) As t
)
Select *
From @Buyers b
Inner Join cte On cte.buyerid = b.buyer_id And cte.orderid = b.order_id
Where rn <= 3;
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2008 at 5:35 pm
Jeffrey,
I know the buyer should never have the same orderid, but if you insert several duplicate records it will not work. Try running your query after adding a few duplicates to the insert. It is an easy fix with a Distinct, I just wanted to bring it up in case the real data may have duplicates.
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 12, 3)
INSERT INTO BUYERS VALUES (1, 12, 3)
July 13, 2008 at 6:00 pm
Ken Simmons (7/13/2008)
I think this is what you are looking for.Select Buyer_ID,Order_ID
FROM (
select order_id, buyer_id,
RANK() OVER (partition BY buyer_id Order by order_id) Rnk
from BUYERS
GROUP BY order_id, buyer_id) A
WHERE Rnk < 4
Ken, what if there are multiple items for an order? I know the OP didn't include that in the sample - but I am assuming that there could be multiple items since there is an item_id column.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 13, 2008 at 6:21 pm
item_id should not affect the query because I am grouping by order_id, buyer_id and item_id is not included in the query. I added a few duplicates to test and got the same results. I may be missing something though. Did you get the query to return the wrong results?
July 13, 2008 at 6:28 pm
Yeah, see that now - missed it the first time.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
July 14, 2008 at 9:59 am
Ken,
What I need is the minimum order_id for each buyer and five items for each of the orders.
The result should look something like this:
Buyer_id order_id item_id
1 1 3
1 1 5
1 1 2
1 1 6
1 1 8
2 1 5
2 1 3
2 1 2
2 1 4
2 1 7
3 1 2
3 1 9
3 1 6
3 1 7
3 1 8
What your query brings me is that for some buyers the same order_id is repeated five times, which is what I need, but in others it could be two different order_ids, one repeated twice, the other one three times, and in some cases it’s just five different order_ids displayed for the same buyer.
July 14, 2008 at 12:57 pm
Levsha_z,
You appear to have changed your requirement. First, you asked for:
The task is to display sample 3 records of the first (minimum) order for each buyer.
Now, you're asking for something else, with a rather obvious change in the number of output records per order (from 3 to 5). I'm now not even sure what, exactly, it is that you seek. Are you looking for a sampling of records, or a specific set of records for each order, and what criteria will identify those 5 (or is it 3?) records? Keep in mind that your original sample data and currently desired output don't appear to match in the least... it can at least help explain why we're confused.
Steve
(aka smunson)
:):):)
levsha_z (7/14/2008)
Ken,What I need is the minimum order_id for each buyer and five items for each of the orders.
The result should look something like this:
Buyer_id order_id item_id
1 1 3
1 1 5
1 1 2
1 1 6
1 1 8
2 1 5
2 1 3
2 1 2
2 1 4
2 1 7
3 1 2
3 1 9
3 1 6
3 1 7
3 1 8
What your query brings me is that for some buyers the same order_id is repeated five times, which is what I need, but in others it could be two different order_ids, one repeated twice, the other one three times, and in some cases it’s just five different order_ids displayed for the same buyer.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 14, 2008 at 1:16 pm
Hi Steve!
You are right - I do sound confusing and I'm sorry about that. :blush:
Three or five - doesn't matter. Let's make it three.
It's sampling data with no particular criteria.
Let's say there are three fields in the table - buyer_id, order_id and item_id.
Each buyer is attributed with several orders, and each order - with several items.
What I need is to display three sample records for the first (minimum) order for each buyer with with three items for each order.
:w00t:
July 14, 2008 at 1:21 pm
See your other post topic for the query I tried to develop once I realized what you were looking for.
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 14, 2008 at 1:27 pm
Already looking!
Thank you! 🙂
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply