Separate rows depend on Previous Row

Question

Post reply

Separate rows depend on Previous Row

Viewing 6 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 1

Thanks for providing the data creation scripts and your desired results. That is very helpful!

I do still need the answer to the question in my previous post's EDIT.

Do you need this exact order?

6    Campaign6    Advertizing
1    Campaign1    Beverage
3    Campaign3    Food
5    Campaign5    Hot Drinks
2    Campaign2    Beverage
7    Campaign7    Marketing
4    Campaign4    Beverage

Or would any order that avoided consecutive categories be fine?

For example, is this ok?

1    Campaign1    Beverage
6    Campaign6    Advertizing
2    Campaign2    Beverage
7    Campaign7    Marketing
4    Campaign4    Beverage
5    Campaign5    Hot Drinks
3    Campaign3    Food

If not, what further rules do you have about the desired order? So far you've only expressed that consecutive identical categories should be avoided as much as possible.

Cheers!

ikrami2000 Default port Points: 1461 More actions · Answer 2

No any order, just i need to avoided consecutive categories

ikrami2000 Default port Points: 1461 More actions · Answer 3

ikrami2000

Default port

Points: 1461

July 15, 2017 at 3:43 am

#1951031

Any Help?

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Something like this should work just to minimize the number of consecutive category_sorts as much as possible:

WITH counted AS (SELECT cnt=COUNT(*) OVER (PARTITION BY category_sort),* FROM @yourtable), numbered AS (SELECT *, mx=MAX(cnt) OVER (),rn=ROW_NUMBER() OVER (ORDER BY cnt DESC) FROM counted)

SELECT id, [name],category_sort FROM numbered ORDER BY rn%mx,cnt;

Cheers!

ikrami2000 Default port Points: 1461 More actions · Answer 5

ikrami2000

Default port

Points: 1461

July 16, 2017 at 4:08 am

#1951051

Perfect Jacob

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 6

I'm glad that helped.

To be clear, though, it's definitely not perfect, for a couple reasons 🙂

First, and most importantly, that exact query will fail sometimes (i.e., it will return consecutive category_sorts when it doesn't have to).

That's because I mistakenly copy/pasted one of the early versions of the query I was playing with.

The problem with that particular query is that the technique used relies on the intermediate sort order (the ROW_NUMBER on which modulo is performed) arranging each category_sort's rows in a contiguous group, with the groups arranged in descending order of their number of rows.

It can happen, though (and on larger sets becomes nearly inevitable) that when there are ties on the number of rows for multiple category_sorts, not all the rows for a given category_sort will be consecutive. When that condition is not met, the method no longer works.

That can be fixed quite simply by adding a secondary sort on the category_sort column for the ROW_NUMBER's ORDER BY.

Similarly, in the final sort order, when there are ties on the rn%mx,cnt sort order, nothing in that query prevents the last category_sort in one group from being sorted first in the next group, resulting in an unnecessary consecutive category_sort.

Again, the fix is adding a sort on the category_sort column for the outer query (so, a tertiary sort on the category_sort column, in addition to the existing rn%mx, cnt sort).

Second, while using the windowed aggregates has the nice effect that the base table is only read once, as the base table grows larger and larger its use of a worktable can reach a point where it's actually outperformed by a query that avoids windowed aggregates but reads the base table multiple times (do also note, if you run tests on large sets of data and continue to use table variables as these sample data setups have, then you'll want to add an OPTION (RECOMPILE) hint to any queries so that the query plans at least use updated cardinality information for the table variable).

Here are those two approaches with the fixes for the first problem applied:

WITH counted AS (SELECT cnt=COUNT(*) OVER (PARTITION BY category_sort),* FROM @yourtable), numbered AS (SELECT *, mx=MAX(cnt) OVER (),rn=ROW_NUMBER() OVER (ORDER BY cnt DESC, category_sort ASC) FROM counted)

SELECT id, [name],category_sort FROM numbered ORDER BY rn%mx,cnt,category_sort;

WITH counted AS (SELECT cnt=COUNT(*),category_sort FROM @yourtable yt GROUP BY category_sort), maxed AS (SELECT mx=MAX(cnt) FROM counted)

SELECT yt.id,yt.category_sort, yt.[name] FROM @yourtable yt INNER JOIN counted c ON yt.category_sort=c.category_sort CROSS APPLY (SELECT mx FROM maxed) mx ORDER BY ROW_NUMBER() OVER (ORDER BY cnt DESC,yt.category_sort ASC)%mx, cnt,category_sort;

Cheers!