July 6, 2015 at 12:18 am
Hi There,
I need to group up the records randomly into ānā number of batches. That can be done by NTILE, but I want group up similar records in single group.
Say for example, following is the list of records I have in my table which I want to group into 5 batches
A123
A124
A124
A123
A127
After Ntile I will get the below,
Desired output is, Need output like Ntile but all same id should reside in single batch
Even if I n=5, maximum possibility of batches are 3 only.
Thanks in advance
July 6, 2015 at 12:55 am
So do a GROUP BY first and then break the results into groups. After that, 'explode' the results back into multiple rows if needed.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 6, 2015 at 1:25 am
Something like this?
SELECT x.id
, DENSE_RANK() OVER (ORDER BY x.id) as DenseRnk
FROM
(SELECT 'A123' AS ID
UNION ALL
SELECT 'A124'
UNION ALL
SELECT 'A124'
UNION ALL
SELECT 'A123'
UNION ALL
SELECT 'A127') x
And then do like Phil said and join back to the original table to show where to put each item?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply