Is it possible to create a dynamic union statement?

Question

Let's say I have a table like this. We can call it sales:

id  | purchaserID | item |      date   |
1   |      1      |  A1  |  2014-10-31 |
2   |      1      |  A2  |  2014-10-30 |
3   |      1      |  A3  |  2014-10-19 |
4   |      1      |  A2  |  2014-10-26 |
5   |      2      |  B1  |  2014-10-31 |
6   |      2      |  B2  |  2014-10-28 |
7   |      2      |  B3  |  2014-10-30 | 
8   |      1      |  C1  |  2014-10-27 |
9   |      2      |  B4  |  2014-10-01 |

What I want to do is get the latest N (we'll make it 3 in the case) dates of purchase for each purchaserID. I know this is possible, courtesy of other questions on Stack. I know that one way (even if it's not the best way) is to select the latest 3 dates for each purchaser and union them together:

(SELECT * FROM sales WHERE purchaserID = 1 ORDER BY date DESC LIMIT 3)
UNION ALL
(SELECT * FROM sales WHERE purchaserID = 2 ORDER BY date DESC LIMIT 3)

What I want to know - is there a way to make this dynamically union all purchaserIDs? What if ID 3 makes a purchase, now I have to recreate my query to get the latest 3 dates for each purchaser.

I've already built an SQL Fiddle for you to play with.

No. But there are other ways to solve this. See my answer at the other thread. A faster solution than mine would use variables. — Strawberry, Oct 31 '14 at 15:50
@Strawberry thanks. This is not a duplicate question at all in my opinion. My question is about the union, not about how to get top n rows. — AdamMc331, Oct 31 '14 at 16:04
@Strawberry didn't mean to point a finger, but I guess I'm curious on your take? Are users allowed to vote to undo that sort of thing? — AdamMc331, Oct 31 '14 at 16:34
The duplicate has the answer. You don't need a UNION, just GROUP BY each purchaser and select the top N results. — JasonMArcher, Oct 31 '14 at 17:26
@JasonMArcher that wasn't the question. Thanks to strawberry I've realized that what I want is simply just not possible. — AdamMc331, Oct 31 '14 at 17:33

score 0 · Answer 1 · edited Nov 06 '14 at 18:23

I think this does what you are looking for without having to union:

CREATE TABLE #Purchases (
id  int, 
purchaserID int, 
item varchar(2),      
date datetime,
)
INSERT INTO #Purchases VALUES
(1   ,      1      ,  'A1'  ,  '2014-10-31') ,
(2   ,      1      ,  'A2'  ,  '2014-10-30') ,
(3   ,      1      ,  'A3'  ,  '2014-10-19') ,
(4   ,      1      ,  'A2'  ,  '2014-10-26') ,
(5   ,      2      ,  'B1'  ,  '2014-10-31') ,
(6   ,      2      ,  'B2'  ,  '2014-10-28') ,
(7   ,      2      ,  'B3'  ,  '2014-10-30'), 
(8   ,      1      ,  'C1'  ,  '2014-10-27') ,
(9   ,      2      ,  'B4'  ,  '2014-10-01') 

SELECT *
FROM #purchases a
WHERE ((
  SELECT count(*) FROM #Purchases as b WHERE 
    b.date > a.date AND
    b.purchaserID = a.purchaserID
) + 1) <= 3
order by purchaserId, date desc

DROP TABLE #Purchases

Returns:

id  purchaserID item    date
1   1   A1  2014-10-31 00:00:00.000
2   1   A2  2014-10-30 00:00:00.000
8   1   C1  2014-10-27 00:00:00.000
5   2   B1  2014-10-31 00:00:00.000
7   2   B3  2014-10-30 00:00:00.000
6   2   B2  2014-10-28 00:00:00.000

This statement is O(n^2) though. Hope this dataset isn't extraordinarily large.

So this can't be done with a union? At least, not dynamically. — AdamMc331, Oct 31 '14 at 18:31

Is it possible to create a dynamic union statement?

1 Answers1