rank() OVER / PARTITION BY vs CTE for numbered groups 1

[larrydavid]

Basically, the solution for this is to order the cat (catalog) sequence sequentially with the next group being renumbered 1, 2, 3, etc. But as you can see here, if the previous value stops at say 12 and the next value only has 1, the following group picks up with 13, 14, 15 and so on.

198879 HISPANIC BEVERAGE 1
198880 HISPANIC BEVERAGE 2
198881 HISPANIC BEVERAGE 3
198882 HISPANIC BEVERAGE 4
198883 HISPANIC BEVERAGE 5
198884 HISPANIC BEVERAGE 6
198885 HISPANIC BEVERAGE 7
198886 HISPANIC BEVERAGE 8
198887 HISPANIC BEVERAGE 9
198888 HISPANIC BEVERAGE 10
198889 HISPANIC BEVERAGE 11
198890 HISPANIC BEVERAGE 12
198891 HISPANIC SAUCES & SALSA 1
198892 HISPANIC BEVERAGE 13
198893 HISPANIC BEVERAGE 14
198894 HISPANIC BEVERAGE 15
198895 HISPANIC BEVERAGE 16
198896 HISPANIC BEVERAGE 17

My solution at first was to do this which gave the output above where it skips renumbering and picks up from the previous group:

SELECT * FROM
(
select excel_item_id, level1, rank() OVER
(
PARTITION BY Level1
order by excel_item_id
) as cat
from [ImportExcel] where product_offering_id = 31372
) Level
order by excel_item_id

But then this seemed to do the trick with adding a rownum column:
with cte as
(select excel_item_id,level1,rownum,'test' change
from
[ImportExcel]
where
rownum = 1
union all
select
r.excel_item_id,r.level1,r.rownum
,case when r.level1 <> r1.level1 then 'test'
else '' end change
from
[ImportExcel] r
join cte r1 on r.rownum - 1 = r1.rownum)
select * from cte
option (maxrecursion 10000);

But for performance reasons due to querying huge recordsets, I would like to make the rank() OVER/PARTITION functions work for this. So if anyone could suggest this or even another solution I would greatly appreciate it.

Thanks,
Larry

 

[bborissov]

Following group is the same as first one. That is why you get the next number.

Could you post some example data and desired result from it?

Borislav Borissov
VFP9 SP2, SQL Server

 

[larrydavid]

Hi Mr. Brissov and all,

Here is sample input:

excel_item_id product_offering_id level1 level2 itemcode cat1
199021 31372 HISPANIC PEPPERS PEPPERS 313179 274791319
199022 31372 HISPANIC BEVERAGE JUICES 313180 274791328
199023 31372 HISPANIC PEPPERS PEPPERS 313181 274791319
199024 31372 HISPANIC PEPPERS PEPPERS 313188 274791319
199045 31372 HISPANIC SAUCES SPECIALTY 313227 274791325
199046 31372 HISPANIC SAUCES SPECIALTY 313228 274791325
199047 31372 HISPANIC PEPPERS PEPPERS 313229 274791319
199048 31372 HISPANIC PEPPERS PEPPERS 313230 274791319
199049 31372 HISPANIC PEPPERS PEPPERS 313231 274791319
199050 31372 HISPANIC PEPPERS PEPPERS 313232 274791319
199051 31372 HISPANIC BEVERAGE COFFEE 313233 274791319
199052 31372 HISPANIC BEVERAGE COFFEE 313235 274791319
199053 31372 HISPANIC BEVERAGE COFFEE 313236 274791319
199054 31372 HISPANIC BEVERAGE ICE POPS 313237 274791319
199055 31372 HISPANIC PEPPERS PEPPERS 313239 274791319
199056 31372 HISPANIC PEPPERS PEPPERS 313240 274791319
199057 31372 HISPANIC PEPPERS PEPPERS 313241 274791319
199058 31372 HISPANIC PEPPERS PEPPERS 313242 274791319
199059 31372 HISPANIC PEPPERS PEPPERS 313244 274791319
199060 31372 HISPANIC SAUCES GLASS SAUCES 313248 274791325
199061 31372 HISPANIC SAUCES GLASS SAUCES 313249 274791325
199062 31372 HISPANIC SAUCES GLASS SAUCES 313250 274791325
199063 31372 HISPANIC SAUCES POURABLE 313251 274791325
199064 31372 HISPANIC PEPPERS PEPPERS 313252 274791319
199065 31372 HISPANIC PEPPERS PEPPERS 313253 274791319
199066 31372 HISPANIC PEPPERS PEPPERS 313255 274791319
199067 31372 HISPANIC SAUCES GLASS SAUCES 313258 274791325
199068 31372 HISPANIC BEANS CANNED BEANS 313261 274791318
199069 31372 HISPANIC BEANS CANNED BEANS 313262 274791318

Here is desired sample output (for a new excel_item_id grouping the cat column should start at 1 again instead of continuing to pick up the previous numbering sequence for the same level product):

excel_item_id level1 cat
198879 HISPANIC BEVERAGE 1
198880 HISPANIC BEVERAGE 2
198881 HISPANIC BEVERAGE 3
198882 HISPANIC BEVERAGE 4
198883 HISPANIC BEVERAGE 5
198884 HISPANIC BEVERAGE 6
198885 HISPANIC BEVERAGE 7
198886 HISPANIC BEVERAGE 8
198887 HISPANIC BEVERAGE 9
198888 HISPANIC BEVERAGE 10
198889 HISPANIC BEVERAGE 11
198890 HISPANIC BEVERAGE 12
198891 HISPANIC SAUCES & SALSA 1
198892 HISPANIC BEVERAGE 1
198893 HISPANIC BEVERAGE 2
198894 HISPANIC BEVERAGE 3
198895 HISPANIC BEVERAGE 4
198896 HISPANIC BEVERAGE 5

Here is the original query with level2 added. I am trying to use which again is not renumbering for a new group:

SELECT * FROM
(
select excel_item_id, level1, level2, rank() OVER
(
PARTITION BY Level1,Level2
order by excel_item_id
) as cat
from importexcel where product_offering_id = 31373
--order by excel_item_id
) Level
--where cat=1
--group by [Level1]
order by excel_item_id
Thanks,
Larry

 

[SQLScholar]

I am not sure this would be possible (i may be wrong). Without recursion\CTE\Cursor SQL works in batches of data, not at individual row level. A row does not know what the previous row was.

Would love to be proved wrong however

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[drlex]

As Bborisov said "Could you post some example data and desired result from it? "

The data that you post as a sample input does not appear to match the sample output, as the output range (198879-96) precedes the input.

soi là, soi carré

 

[gk53]

it is still unclear what you need...
What column cat means in your last query?, is that runk or rownumber or min(cat1) or max(cat1)?
and where is the problem?
your last query equivalent to

select  excel_item_id, level1, level2, 
	rank() OVER (PARTITION BY Level1,Level2 order by excel_item_id ) as cat
	from importexcel where product_offering_id = 31372
	order by level1, level2

if you remove unnessesary select * from....
and your code produce with your data no output but with product_offering_id = 31372 it give me

excel_item_id level1 level2 cat
199021 HISPANIC PEPPERS PEPPERS 1
199022 HISPANIC BEVERAGE JUICES 1
199023 HISPANIC PEPPERS PEPPERS 2
199024 HISPANIC PEPPERS PEPPERS 3
199045 HISPANIC SAUCES SPECIALTY 1
199046 HISPANIC SAUCES SPECIALTY 2
199047 HISPANIC PEPPERS PEPPERS 4
199048 HISPANIC PEPPERS PEPPERS 5
199049 HISPANIC PEPPERS PEPPERS 6
199050 HISPANIC PEPPERS PEPPERS 7
199051 HISPANIC BEVERAGE COFFEE 1
199052 HISPANIC BEVERAGE COFFEE 2
199053 HISPANIC BEVERAGE COFFEE 3
199054 HISPANIC BEVERAGE ICE POPS 1
199055 HISPANIC PEPPERS PEPPERS 8
199056 HISPANIC PEPPERS PEPPERS 9
199057 HISPANIC PEPPERS PEPPERS 10
199058 HISPANIC PEPPERS PEPPERS 11
199059 HISPANIC PEPPERS PEPPERS 12
199060 HISPANIC SAUCES GLASS SAUCES 1
199061 HISPANIC SAUCES GLASS SAUCES 2
199062 HISPANIC SAUCES GLASS SAUCES 3
199063 HISPANIC SAUCES POURABLE 1
199064 HISPANIC PEPPERS PEPPERS 13
199065 HISPANIC PEPPERS PEPPERS 14
199066 HISPANIC PEPPERS PEPPERS 15
199067 HISPANIC SAUCES GLASS SAUCES 4
199068 HISPANIC BEANS CANNED BEANS 1
199069 HISPANIC BEANS CANNED BEANS 2

Or may be you need just

;with cte as
(
	select MIN(excel_item_id) as  min_excel_item_id, level1, level2, COUNT(*) as cat
	--	rank() OVER (PARTITION BY Level1,Level2 order by excel_item_id ) as cat
		from @T where product_offering_id = 31372
		group by level1, level2
)
select * from cte
	order by cat, level1, level2

is that what you are looking for?

 

[larrydavid]

Thank you all for your valuable feedback and help. Much appreciated! --Larry