[@hiyaborah9167]

Hi Ankit, I have another solution to this problem:
with cte1 as (
select * ,
row_number() over(order by (select null)) rn
from brands
)
select min(category) over(order by rn rows between unbounded preceding and current row) category,
brand_name
from cte1
Wasn't really confident using rows clause but thanks to your videos I am learning new concepts everyday.

[@ankitbansal6]

Awesome

[@stat_life]

@@ankitbansal6
with temp as
(
select * ,
row_number() over(order by (select null)) rn
from brands
)
select min(category) over(order by rn) category,
brand_name
from temp
sir, won't this query pretty much gives the same solution?
why did we used rows between unbounded preceding and current row here ?

[@LittleBrainFeeds]

Good solution

[@subhojitchatterjee6312]

Easy to understand ,but this will not work in all cases. If the category name changes something to "Freshly Packed" from "Biscuits" then it won't work.

[@ank_kumar12]

i got the same result from this query
with tab1 as (select *,
count(t.category) over(order by t.rnk) as cnt from(
select *,
row_number() over() as rnk
from choco) t)
select category,brand,
first_value(category) over(partition by cnt order by rnk)
from tab1;

[@pauldfm1970]

Lovely to see a solution in sql for something I have solved in excel for years.

[@Howto-ty4ru]

True

[@venkataram6460]

Hi Ankit,
I have solved this using below method, you have tought this method for another problem.
with cte1 as
(select *,
row_number() over(order by (select null)) as id,
case when category is null then 0 else 1 end as rn
from brands
)
,cte2 as
(select *
,sum(rn) over(order by id) as roll_sum
from cte1
)
select brand_name,
max(category) over(partition by roll_sum) as category
from cte2

[@kingsleyarisah6354]

Great solution to this tricky problem

[@swapnilshimpi7588]

Good use case to practice Ankit,
I practiced using combination of rowunm ,join and understood how it works and then able to come up with one line solution .
Done using oracle -
select nvl(category,lag(category) ignore nulls over(order by null) ) category,brand
from
brands ;
or
select category,brand,rownum rn1, last_value(category) ignore nulls over ( order by rownum asc rows between unbounded preceding and current row )
from brands;
Your solution helped to derive the approach. So though I knew how last_value , first value worked , first time I found out use case where rows between... clause and ignore null was useful .
Thanks for providing this example!

[@ankitbansal6]

Great 😊

[@namanmakkar1205]

Can you explain ignore null

[@swapnilshimpi7588]

@@namanmakkar1205 Ignore null , as indicates it ignores null in that window and looks for rows with actual value

[@shekharagarwal1004]

@@swapnilshimpi7588 Can you help to get the same in MY SQL syntax. It is not accepting the ignore null part .

[@shubhamagrawal7068]

In this approach, I am using co-related subquery. I haven't use any join. This code is very small.........
with t1 as (select *, row_number() over() rn from brands)
select
(case
when category is null then (select category from t1 b where b.rn < a.rn and category is not null order by rn desc limit 1)
else category
end) category, brand_name
from t1 a

[@ankimedia5225]

What sub query will return please explain

[@ankimedia5225]

@Subham Agrawal

[@abdulqadar559]

Hi Ankit, I solved it using running sum
with t1 as
(
select *,row_number() over () rn from brands
),
t2 as
(
select *,sum(case when category is not null then 1 else 0 end) over (order by rn) flag from t1
)
select max(category) over (partition by flag) as category,brand_name from t2

[@edumail1016]

One correction - with t1 as
(
select *,row_number() over (ORDER BY (SELECT NULL)) rn from brands
),
t2 as (
select *,sum(case when category is not null then 1 else 0 end) over (order by rn) flag from t1
)
select max(category) over (partition by flag) as category,brand_name from t2

[@abdulqadar559]

@@edumail1016Yes

[@vijaygupta7059]

using CTE and aggregate (count) window function
with cte as(
Select *
,count(category)over(order by (Select null) rows between unbounded preceding and 0 following ) as rn
from brands
)
Select first_value(category)over(partition by rn order by rn ) as category, brand_name from cte

[@Chathur732]

this is simple . great job!!

[@kingsleyarisah6354]

Real smart stuff

[@parthchamp9252]

WITH temp1
AS (SELECT *,
Row_number()
over(
ORDER BY NULL) AS start
FROM brands),
temp2
AS (SELECT *
FROM temp1
WHERE category IS NOT NULL),
temp3
AS (SELECT *,
( Lead(start, 1, 9999)
over (
ORDER BY start) - 1 ) AS END
FROM temp2)
SELECT b.category,
a.brand_name
FROM temp1 a
join temp3 b
ON a.start BETWEEN b.start AND b.END;

[@mantisbrains]

select b.category,c.brand_name from
(select *, lead(row_number) over (order by row_number) as ranges from
(select * from
(select *,row_number() over () from brands)a
where category is not null)k)b
inner join
(select *,row_number() over () from brands)c
on c.row_number >= b.row_number and (c.row_number

[@KapilKumar-hk9xk]

Hi Ankit, another solution:
with cte2 as(
with cte as
(select *, row_number() over() as rwn from brands)
select *, count(category) over(order by rwn) as cnt from cte
)
select first_value(category) over(partition by cnt) as category, brand_name from cte2;

[@saigusidi9122]

select (case: when category is null then category= lag(category) over()
else category
end) as category, chocolate from table.

[@vivekkumarsingh9009]

I used the same technique like the ON OFF problem here, tried to maintain a simple strategy for creating a sequence for creating groups. Its just easier for me to remember.
with cte as (select *,row_number() over() as rowed from brands),
cte2 as (
select *
,sum(case when category is not null then 1 else 0 end) over(order by rowed) as grouped
from cte)
select max(category) over (partition by grouped),brand_name
from cte2;

[@ankitbansal6]

Good one

[@siddheshkalgaonkar2752]

Interesting solution, mate. I was surprised to see that max works on the string too...

[@DEwithAbhishekTripathi]

Hi Ankit, can we do this way........
Select (case when rn>1 and rn5 and rn

[@litheshraju5207]

used between for join:
with cte as(
select *,ROW_NUMBER() over(order by (select null)) rn
from brands)
,cte1 as(
select *,lead(rn-1,1,9999) over(order by rn) btw
from cte
where category is not null
)
select c1.category,c.brand_name
from cte c inner join cte1 c1 on c.rn between c1.rn and c1.btw

[@ankitbansal6]

Looks good

[@surensurendar7417]

other solution using first value:-
with cte as (
SELECT category,brand_name,
row_number() over(ORDER BY NULL) as rn
FROM brands
)
select category,brand_name,
first_value (category) IGNORE NULLS
over(order by rn desc
range between current row and unbounded following) as result
FROM cte
ORDER BY rn
;

[@sathwikdhanya5400]

using only window function:
with cte as(select *,
ROW_NUMBER() over(order by (select 1)) as id
from brands),
cte2 AS (select *,
sum(case when category is NULL then 0 else 1 end) over(order by id) as part
from cte)
SELECT *,
FIRST_VALUE(category) over(partition by part order by id) as fill_NA
from cte2

[@pallavibs4673]

Hi,
Help me to understand how did inner join between CET1 result and CTE 2 result replaced NULL with category name.. If possible please make a short video on query execution.

[@tahakhalid324]

Hi Ankit, here is my solution : with rnks as (
SELECT
category,
brand_name,
COUNT(category)over(rows between unbounded preceding and current row) as rnks
from brands
)
SELECT
first_value(category)over(partition by rnks) as category,
brand_name
from rnks

[@siddharth_1899]

with cte1 as (
select row_number() over(order by (select null)) as rn, *
from brands
)
, cte2 as (
select *
, count(category) over(order by rn) as cnt
from cte1
)
select FIRST_VALUE(category) over(partition by cnt order by rn ) as category
, brand_name
from cte2

[@gouravsinha268]

with cte as
(SELECT
*
, SUM(CASE WHEN category IS NULL THEN 0 ELSE 1 END) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) flg
FROM brands)
SELECT
FIRST_VALUE(category) OVER(PARTITION BY flg)
, brand_name
FROM cte;
How about this one Ankit?

[@siddhartharao8490]

Thanks bro for the brief explanation!

[@shashankmp6668]

hi ankit, i need to get clarification Microsoft Sql Server is the platform where we can write the sql queries or what is. Microsoft sql server can you give me a brief idea about this

[@saurabhpandey5296]

with cte as
(select *, row_number() over(order by (select null)) as rnum
from brands),
cte2 as
(select *, sum(case when category is not null then rnum end) over(order by rnum) as flag
from cte)
select first_value(category) over(partition by flag order by flag) as category_new, brand_name
from cte2

[@rohitsharma-mg7hd]

with cte as (
select category,lag(category) over() as prev_category,row_number()over() as rn ,brand_name
from brands)
,cte2 as
(select *,sum(case when category is not null and prev_category is null then 1 else 0 end) over(order by rn) as flag from cte)


select first_value(coalesce(category,0::text)) over(partition by flag),brand_name from cte2

[@rawat7203]

Hi Sir My Way:
select
case
when partition_flag = 1 then 'choclates'
when partition_flag = 2 then 'Biscuits'
end as 'category',
brand_name from (
select *,
case when category is not null then 1 else 0 end as flag,
sum(case when category is not null then 1 else 0 end) over(rows between unbounded preceding and current row) as partition_flag
from brands)temp;

[@nithinkumar6555]

with CTE1 as (select *,row_number()over() as rn,case when lead(category)over() is null
and category is not null then 1 else 0 end as num from brands),
CTE2 as (select category,brand_name,sum(num)over(order by rn) as sm from CTE1)
select b1.category,b2.brand_name from CTE2 b1 left join CTE2 b2 on b1.sm=b2.sm
where b1.category is not null

[@GowthamR-ro2pt]

Easiest approach would be :
with cte as (select *,ROW_NUMBER() over(order by (select null))rn from brands),cte2 as (
select *,count(category) over (order by rn) cnt from cte)
select *,FIRST_VALUE(category) over (partition by cnt order by cnt)result from cte2

[@francis.joseph]

can you do a lead lag master class video?

[@ankitbansal6]

It's there already

[@dfkgjdflkg]

Very interesting what you mention, which gives me hope to become better as solving SQL problems. You said you could not solve this problem. I guess I have to work harder on the ones I can not solve and work harder. Thanks for the inspiration, as usual.

[@ankitbansal6]

There is always a learning curve 😊

[@komalpawar5124]

I tried to solve in different way and here my solution :;with cte as
(select ROW_NUMBER() over(order by brand_name) as rw,* from brands)
, cte2 as(
select category ,brand_name,sum ( case when category is null then 0 else 1 end ) over(order by rw) rww from cte)
select FIRST_VALUE(category) over(partition by rww order by (select 1)) as Category, brand_name
from cte2

[@deepanshubanyal7109]

But I think there is one problem in ranking by select null approach as it give provide nondeterministic ordering, means it will not provide row number as the data stored physically. So we will already have id column then this solution is perfect

[@Satish_____Sharma]

using mysql
with cte as (SELECT category, brand_name,row_number() over () as rn
FROM brands)
,cte1 as (select *,sum(case when category is not null then 1 else 0 end) over (order by rn) as ct from cte)
select first_value(category) over (partition by ct order by rn) as category,brand_name from cte1

[@PaulFilmer]

I don't believe any solution should rely on the default return order of data to make an assumption on what the correct value would be. In this situation, the only proper course of action is to find the original source data, manually identify, or use a master data source of all chocolate & biscuit brands to match and validate.

[@smahourkumar2568]

select category1,brand_name from (
select * ,max(category) over(partition by t ) as category1
from (
select *,sum(case when category is not null then 1 else 0 end ) over(order by rn) as t from
(select *,row_number() over() as rn from brands)))

[@sajanpradhan1539]

this works but does it a good query???
with a as (
select *,
row_number() over() as remark
from brands)
select if (category=null,'chocolates','chocolates') category,brand_name
from a where remark between 1 and 4
union all
select category,brand_name from (
with b as (
select *,
row_number() over() as remark
from brands)
select if (category=null,'Biscuits','Biscuits') category,brand_name
from b where remark between 5 and 7) as abc

[@shubhamsom]

Hi Ankit, I have another solution
I am using running sum to make all nulls be part of same group as of previous non null value
(I learned this from you only)
with brands_rn as (
select *, row_number() over() as rn
from brands
),
brands_grouped as (
select *,
sum(case when category is null then 0 else 1 end) over(order by rn) as gpno
from
brands_rn
)
select
max(category) over(partition by gpno) as category,
brand_name
from brands_grouped

[@vanshhans5676]

I thought of the same but this will not work if there are more than 2 categories.

[@eRRor4hack]

Alternate Solution: with structureCte as( --creating structure/skeleton so that "order by" can be used
select *,
ROW_NUMBER() over(order by(select null)) as rn
from brands
)
,categoryGrp as(
select *,
count(category) over(order by rn asc rows between unbounded preceding and current row) as grp --grouper(so that partitioning can be done)
from structureCte
)
select
FIRST_VALUE(category) over(partition by grp order by rn) as Category_filled, --Get First_Value in a particular group
categoryGrp.brand_name
from categoryGrp

[@anjulaxmipillai8683]

with cte as(
select *,row_number() over(ORDER BY (SELECT NULL)) as rn from brandS)
select brand_name, MIN(category) over(order by rn) as category from cte

[@vikassolanki297]

With cte as(
select *, row_number() over(order by (select null)) as rn
from brands)
select min(category) over(partition by cnt order by rn) from (
select *, count(category) over(order by rn) as cnt from cte) a

[@akashvishwakarma1317]

with
base as
(
Select
*,
case
when category is not null
then 1
else 0
end as flag
from brands
),
base2 as
(
Select
*,
sum(flag) over(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_sum
from base
)
Select
first_value(category) over(partition by running_sum) as category,
brand_name
from base2

[@ankitbansal6]

Good one 👍

[@felipepereira3061]

Thanks a lot Ankit! Yout vídeo simply solved a problem that i had to make a report query on my job! THANK YOU! =D

[@ankitbansal6]

Happy to help😁

[@rajeshvodela91]

with cte as (
select *,
row_number() over() as rn
from brands
)
select
case
when rn between 1 and 4 then 'chocolates' else 'biscuits'
end as category,
brand_name
from cte;
i did like this is this correct

[@abb_raj1107]

Bhai ! one thing I want to know is row_number() fun also work with over() clause without adding order by in it , what is the difference between the empty over() and over(order by (select null)).
btw .... thanks u so much

[@ankitbansal6]

Doesn't work in SQL server

[@LastCall-z3i]

with cte as (
select category,brand_name,
row_number() over (order by (select null)) as product_id
from brands)
, cte2 as (
select category,brand_name,product_id,
count(category) over (order by product_id) as category_group
from cte)
select
first_value(category) over (partition by category_group order by product_id) as category,
brand_name
from cte2;

[@GaneshNaduvathati]

;with cte as(
select *,ROW_NUMBER()over(order by (select null)) as rw from brands
),cte1 as(
select *,count(category)over(order by rw) as cnt from cte
)
select *,FIRST_VALUE(category)over(partition by cnt order by rw) from cte1

[@sreejitchakraborty6575]

Hi Can you check this:
select max(category) over(partition by ct),brand_name from
(
select category,brand_name,
count(category) over(rows between unbounded preceding and current row) ct from brands
)q

[@amarjadhav3955]

Hi sir you can use the first_value() function to solve this issue in a simple way.

[@2412_Sujoy_Das]

I derived by the same solution Sir!!!!!!!!!!
WITH CTE_1 AS (Select *, ROW_NUMBER() OVER(order by (SELECT NULL)) as rnk from brands),
CTE_2 AS (Select category, rnk, COALESCE(LEAD(rnk) OVER(order by rnk),999999) as next_one
from CTE_1 WHERE category IS NOT NULL)
Select B.category, A.brand_name
FROM CTE_1 A
JOIN CTE_2 B
ON A.rnk >= B.rnk AND (A.rnk

[@anirbanbiswas7624]

ONE EASIEST APPROACH AS SUM BETWWEN 1 AND 0 MAKES IT EASIER,KINDLY CHECK THIS OUT
with cte as(select *,sum(case when category is not null then 1 else 0 end)
over(order by brand_name)as segment from brands_new)
select first_value(category) over(partition by segment)as new_cat,brand_name from cte

[@shivanandkumar6002]

Much needed for today 😂

[@nivadonga6980]

Hi, here is my solution:
with cte as
(select *, row_number() over(order by (select null)) as rn from brands)
select first_value(category) over(partition by partition_variable order by rn) as Category, brand_name from
(select *, sum(case when category is null then 0 else 1 end) over(order by rn) as partition_variable
from cte) A

[@dfkgjdflkg]

Ever thanks for the invaluable example

[@nachiketpalsodkar4356]

ANother Simpler way!!!
select *
, MIN(category)over(order by (select NULL) rows between unbounded preceding and current row) as NEW_CATEGORY
from brands;

[@udhaybhaskarbellamkonda1678]

This works ,great

[@rajveerdhumal3143]

this is not feasible method, I have tried adding few values and its not working

[@rajveerdhumal3143]

insert into brands values
('softdrink','pepsi'),
(null,'coke'),
(null,'sprite');

[@poornimanaidu6923]

Hi Ankit, I solved this problem using user defined variable with case statement. Please check my approach if its efficient -
select case when category is not null then @category := category
when category is null then @category end as category1,
brand_name from brands;

[@debaratiaich16]

this should work fine:
SELECT
COALESCE(category, LAG(category IGNORE NULLS) OVER (ORDER BY brand_name)) AS category,
brand_name
FROM
your_table_name;

[@MovieBuzz-uu8kp]

select *, first_value(category) over (partition by sm order by rw) as new from (
select *, sum(id) over (order by rw) as sm from (
select * , case when category is not null then 1 else 0 end as id from (
select *, row_number ()over (order by (select null)) as rw
from brands ) a ) b ) c

[@mohitupadhayay1439]

In power BI, you just need to click on the column and click 'Fill values'. Simple.

[@ankitbansal6]

Cool

[@ishanksaxena3329]

But Ankit said that it's a SQL interview so we have to solve this problem with sql😄

[@vikaskumar-qr5tj]

🤣

[@Kishansinghbhandari]

​@@ishanksaxena3329😂

[@gunasekharreddy230]

Hello , This Problem was asked to do in DAX for me in EXL Interview

[@ramjigupta174]

Here is my solution for this interesting problem:
with cte as (
select category,brand_name,
case when category is not null then 1 else 0 end as flag,
row_number() over() as rn
from brands)
,cte2 as (
select category,brand_name,sum(flag) over (order by rn) as grp from cte)
select c3.category,c2.brand_name from cte2 as c2 join cte2 as c3 on c2.grp=c3.grp and c3.category is not null;

[@hasanmougharbel8030]

Hey dear, god bless your efforts in this channel.
I have a general enquiry as a new sql learner.
How could i create a pipeline to extract and load data from existing accounting program into our SQL server instances.
How can i know if the export mechanism in the software permits me to undertake this extraction process, and how can i know if an application have an api?
Thanks for taking care of my enquires.
Looking forward to gain more knowledge from you.

[@atharvadeshpande7324]

Hi Ankit, row_number with ordering by (select null) does not work in redshift SQL. Redshift considers the whole table under order by and then gives row numbers. Is there any alternative to designate such serial numbers which will work in redshift?

[@ankitbansal6]

You can do order by true in redshift

[@Alexpudow]

HI, I solved that by recursive cte 🙂
with a as (
select *
,case when category is not null then row_number() over(order by (select null)) end crn
,row_number() over(order by (select null)) cn
from brands),
b as (
select max(cn) mxcn
from a),
c as (
select category, crn mn, lead(crn,1, (select mxcn from b)+1) over(order by crn)-1 mx
from a
where category is not null),
rec as (
select category, mn, mx
from c
union all
select category, mn+1, mx
from rec
where mn

[@shivprasadshelgavkar4816]

SELECT
a.category,
a.brand_name,
MAX(a.category) OVER (PARTITION BY a.RN ORDER BY a.RN) newcategory
FROM (
SELECT *
, SUM(CASE WHEN COALESCE(category,'') = '' THEN 0 ELSE 1 END) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING and current row ) RN
FROM brands
) a
;

[@anirvansen2941]

MY MYSQL Solution
with base as (select *,case when category is not null then 1 else 0 end as flag from brands),
base_rank as (
select *,sum(flag) over(rows between unbounded preceding and current row) as run_sum from base )
select coalesce(category,max(category) over(partition by run_sum)) as category,brand_name from base_rank;

[@kurakulaprasad7898]

Thank you so much Sir for the wonderful query

[@vijaypalmanit]

Bhai video dekhta hu to sab samajh me aa jata hai par kuchh din baad bhul jata hu

[@rajveerdhumal3143]

thanks ankit it works well

[@singh_dataengineering_tips]

I tried solving this using while loop, try this one:
select *,row_number() over(order by (select null)) as rw into #brands from brands
--drop table #brands
select * from #brands
declare @rw int
set @rw=1
while (@rw

[@MrChilo89]

looking a way to create a dynamic sql and do it on every column of a table

[@anish_bhateja]

my solution:
with cte as (select *,row_number() over () as num from deloitte_brands),
brands as (select category as type,num as row_num from cte where category is not null), cte2 as (select *,lead(row_num,1,9999) over() as next_row_num from brands)
select type,brand_name from cte2,cte where (cte.num >= cte2.row_num and cte.num < cte2.next_row_num ) order by type;

[@PiyushSharma-jq8rr]

Thanks Ankit but we can write it another way 🙂..simple with CASE statement.
WITH t1 AS(
select category, brand_name,
row_number() over(order by (select null from dual)) as rnk
from brands)
SELECT CASE
WHEN rnk = 1 THEN 'chocolates'
WHEN rnk = 2 THEN 'chocolates'
WHEN rnk = 3 THEN 'chocolates'
WHEN rnk = 4 THEN 'chocolates'
ELSE 'Biscuits'
END AS category,
brand_name, rnk
FROM t1;

[@ankitbansal6]

But you have hard coded the values here. What if more products come in tables

[@Shivani-yk7tw]

here is my solution
with t1 as
(
select *,row_number() over (order by (select null)) rn from brands
),
t2 as
(
select *,sum(case when category is not null then 1 else 0 end)
over (order by rn) flag from t1
)
select * from t2
select max(category) over (partition by flag) as category,brand_name from t2

[@prabhatgupta6415]

select category,brand_name from (select brand_name,
case when brand_name in ('5-Star','dairy milk','perk','eclair') then 'choclates' else 'Biscuits' end as category from brands1)x
Hi Ankir Sir cant we do like this please verify

[@ankitbansal6]

It's hard coding. Write a generic code

[@siddheshkalgaonkar2752]

Good problem, Ankit :)

[@varunas9784]

My attempt on SQL server:
===============================
with partition_flag as (select *,
ROW_NUMBER() over(order by (select 1)) [rn],
case when category is not null then 1 else 0 end [partition flag]
from brands),
group_values as (select *,
SUM([partition flag]) over(order by [rn]) [group]
from partition_flag)
select FIRST_VALUE(category) over(partition by [group] order by [group]) [Category],
brand_name
from group_values
===============================

[@mohdanas7198]

WITH CTE AS
(SELECT *,NTILE(2) OVER() as Bucket from Your_Table)
,CTE2 AS
(SELECT * ,CASE WHEN (Bucket = 1 and category IS NULL)
or (Bucket = 1 and category IS not NULL) then 'Choclate'
else 'Biscuite' end as Category1
from CTE)
SELECT category1 AS category
,brand
FROM CTE2

[@kanchankumar3355]

Such a wonderful trick 👍

[@Deepratan27]

SELECT
CASE
WHEN ROWNUM IN (2, 3, 4) THEN 'chocolates'
WHEN ROWNUM IN (6, 7) THEN 'Biscuits'
ELSE CATEGORY
END AS CATEGORY,
BRAND_NAME
FROM brands; working fine in Oracle Sql.

[@umakantyadav9972]

My Solution Using First_value():
with brand as (
select category,COUNT(category) OVER(order by b.rn) as cnt,brand_name,rn
from(
select *,ROW_NUMBER() OVER(order by NULL) as rn
from brands) as b
)
select first_value(brand.category) over(partition by brand.cnt order by brand.rn) as category,brand.brand_name
from brand

[@rahulmehla2014]

great question !!

[@9tanmoy]

Hi Ankit can we do this, if we just make an indexing column like you did with rn and then according to the index we use set function and update the value at once...can we do that

[@ankitbansal6]

Try writing the query 😊

[@sheetalpalod996]

Row_number() over (order by null) is changing the order of names of item and not giving numbering to the exact order that is in table in Netezza. Any suggestions . Tried replacing null with 1, true but still order gets mixed up

[@no-nonsense-here]

try just row_number() over()

[@NitishKumar-l5s9s]

alter table brands add column id int auto_increment primary key;
with recursive filled_categories as (
select id, category, brand_name
from brands
where id = 1
union all
select
b.id,
case when b.category is not null then b.category else fc.category end as category,
b.brand_name
from brands b
join filled_categories fc on b.id = fc.id + 1
)
select
category,
brand_name
from filled_categories
order by id;

[@indergaming3053]

/* simple and easy approach*/
select first_value(category) over (partition by new_count order by rn ) as category ,brand_name from (
select *, count(category) over (order by rn) as new_count from (
select *,
row_number () over (order by (select null)) as rn
from brands
)A)B

[@radhakrishnanselvaraj518]

Simple solution:
select MIN(category) OVER (ORDER BY (SELECT null) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS category,
brand_name FROM brands;

[@chetanphalak7192]

Why cant we use max, can u please explain a bit

[@deepanshugoyal2531]

My solution is more simple i think:
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER() as rn FROM Brands
)
,CTE1 AS (SELECT *, SUM(CASE WHEN category is NULL THEN 0 ELSE 1 END) OVER(ORDER BY rn) as sm FROM CTE)
SELECT FIRST_VALUE(category) OVER(PARTITION BY sm ORDER BY rn) as Brands,brand_name FROM CTE1

[@saurabhsoni1998]

Great. You missed to add order by in over clause and it won't work in SQL Server if it's empty. -- ROW_NUMBER() OVER(order by (select null))

[@deepanshugoyal2531]

It depends on which SQL database you are using

[@ankitbansal6]

Good one 👍

[@dipeshg8581]

select count(category) as category from brands;
set @cat = 2;
with cte as (select category,brand_name,
ntile(@cat) over() as cat
from brands)
select brand_name,
first_value(category) over(partition by cat) as category
from cte
hope you like this solution

[@sankarpatro5602]

Hi Ankit , here is my solution
with CTE as (
Select category, Brand_Name,
ROW_NUMBER () over (order by (select 0)) as rn
from brands)
select brand_name, min(category) over (order by rn) as New_Category from CTE

[@ankitbansal6]

This is working with this particular data only take one more category with name starts from C or D

[@venuamrutham6179]

SELECT @k := IF(category is not null, category, @k) as category,
brand_name FROM table;

[@lakshaykhanna2462]

MySQL solution (Would work in MSSQL as well if 'IF' is replaced by 'Case')
WITH cte_1 AS
(
SELECT category, brand_name, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rn
FROM brands
), cte_2 AS
(
SELECT *,MAX(IF(category IS NOT NULL, rn, NULL)) OVER(ORDER BY RN) AS grp
FROM cte_1
)
SELECT MAX(category) OVER(PARTITION BY grp ORDER BY rn) category, brand_name
FROM cte_2

[@True_living_2112]

use coalesce function which is more simpler than any other process

[@rabink.5115]

could you please update with the queries. It will help a lot. Thanks

[@ShrinchaRani]

@@rabink.5115 WITH CTE AS (
select *,
ROW_NUMBER()OVER(ORDER BY (SELECT NULL)) AS rn
from bars
)
SELECT category,brand_name from (
SELECT coalesce(category,CASE WHEN rn

[@sarabanv]

WITH cte1 AS
(SELECT *, ROW_NUMBER() OVER(ORDER BY null) AS id
FROM brands),
cte2 AS
(SELECT *, COUNT(category) OVER(ORDER BY id) AS grp
FROM cte1)
SELECT MAX(category) OVER(PARTITION BY grp ORDER BY id) AS category, brand_name
FROM cte2

[@naveennoel9496]

Just an observation
To get row number on the whole table the over clause can be left blank too.
select *, row_number() over() from table;

[@SHUBHAM_707]

## SIMPLEST SOLUTION
select *, coalesce(category ,lead(category) IGNORE NULLS over(order by (select null))) from brands

[@darshikashah3012]

In ct2 there is no rn , how can we write cte1.rn>ct2.rn ? , in ct2 we have just next_rn

[@anuragkumar-ti1vz]

In cte2 we are doing select * from cte1 so all columns of cte1 can be accessed from cte2. Now cte1 & cte2 became 2 tables so in the final select we can pick any columns from both tables.

[@GaneshNaduvathati]

;
WITH cte_seq
AS (
SELECT *
,row_number() OVER (
ORDER BY (
SELECT NULL
)
) AS rw
FROM brands
)
,cte_null
AS (
SELECT *
,sum(CASE
WHEN category IS NULL
THEN 0
ELSE 1
END) OVER (
ORDER BY rw
) AS rwn
FROM cte_seq
)
SELECT FIRST_VALUE(category) OVER (
PARTITION BY rwn ORDER BY rwn
) AS Category
,brand_name
FROM cte_null

[@gyanubhai86]

First Solution:
WITH cte1 AS(
SELECT *,
SUM(CASE WHEN category IS NOT NULL THEN 1 ELSE 0 END)
OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS temp FROM brands),
cte2 AS(
SELECT category, temp FROM cte1
WHERE category IS NOT NULL)
SELECT c2.category, c1.brand_name FROM cte1 c1 JOIN cte2 c2
ON c1.temp = c2.temp
Second Solution:
with cte as (select *, COUNT(category) over (order by rn)as cnt
from
(select *, row_number() over (order by (select null))rn from brands)a)

select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

[@vaibhavverma1340]

with cte as
(select *, COUNT(category) over (order by rn)as cnt
from
(select *, row_number() over (order by (select null))rn from brands)a)

select first_value(category) over (partition by cnt order by rn)category_new , brand_name from cte

[@calmedbythewind3453]

but we have first_value() in sql

[@Deepratan27]

SELECT
LAST_VALUE(CATEGORY IGNORE NULLS) OVER (ORDER BY ROWNUM) AS CATEGORY,BRAND_NAME
FROM brands;

[@syedmudasir5702]

WITH CTE1 AS
(SELECT ROW_NUMBER() Over (ORDER BY (select(NULL))) as PRODUCT_ID, category, brand_name
FROM dairy..brands),
CTE2 AS
(SELECT PRODUCT_ID, category, brand_name, COUNT(category) OVER (Order by PRODUCT_ID) as C_group
FROM CTE1)
SELECT FIRST_VALUE(category) OVER ( Partition by C_group Order by PRODUCT_ID) as CATEGORY, brand_name
FROM CTE2

[@KnstntnKrgr]

That saved my ass. Thx ❤