Footer Values - SQL Interview Query 3 | SQL Problem Level "MEDIUM"

[@vishwanath-ts]

I must say your channel is underrated for the work you are putting in.

[@florincopaci6821]

Solution in oracle:
select unique first_value(car ignore nulls)over(order by id desc rows between unbounded preceding and unbounded following)as car,
first_value(length ignore nulls)over(order by id desc rows between unbounded preceding and unbounded following)as len,
first_value(widht ignore nulls)over(order by id desc rows between unbounded preceding and unbounded following)as widht,
first_value(height ignore nulls)over(order by id desc rows between unbounded preceding and unbounded following)as height
from footer;
Hope it helps

[@onwuchekwamalachky7373]

first solution is easy and straight forward

[@HariKrishna-p9e3r]

select distinct LAST_VALUE(car) over (order by (select 0) ) car, LAST_VALUE(length) over (order by (select 0) ) length ,
LAST_VALUE(width) over (order by (select 0) ) width ,LAST_VALUE(Height) over (order by (select 0) ) Height from Footer ,
by this querry we can achive the same thing without id to order
i beleive this one simplyfiy the querry and i want to thank to u , im getting lots through your channel please keep posting such innovative ideas in sql and performance related querry optizations too....

[@sarangtamrakar8723]

I have atlassian interview next month & you are creating this series, True Gem 💎😁

[@hariikrishnan]

Hey bro could you say which role? and how much YoE do you have?

[@Tusharchitrakar]

Excellent initiative. As an aspiring data scientist who is focussing on math and stats apart from ml, this is an excellent way to keep SQL concepts thorough thanks
Solution 3: use group_concat and substring_index (in MySQL) where we can obtain everything after the last delimiter (like comma for example that can be used in the group_concat) which is nothing but the footer value

[@Aadi_07__]

For better understanding, Post solution bro

[@Tusharchitrakar]

@@Aadi_07__ i definitely would have my friend but I'm currently very busy and just focussing on psuedo code unless the problem is too hard and i want to solve it myself. But general idea is to use substring_index (with index -1) which will extract all the letters after the last comma (or any delimiter you choose) when this is imparted on a group_concat (this is in MySQL but i think it is string_agg in SQL server) where all the values will be grouped from a column.
For example,
If i have a column with 5 values ('abc','def',null,'xyz',null,'last word'), group_concat will aggregate all of the above and substring_index of the above result (which can be stored in a cte) with index -1 will return 'last word'
Also ggmu (if you are a united fan, going from your dp, since I am too)

[@AbhishekSharma-dn5us]

@@Tusharchitrakar 😂

[@rajkumarrajan8059]

Superb Toufeeq.. You are humble, simple and polite!!! Amazing

[@vaishnavnichal9813]

Hi Toufeeq, this series of solving interview questions is really helpful. Thank you for this. I really appreciate it. SUper clear,Amzaing!!!!
Just to add here, I have tried solving this question using coalesce function:
WITH LastNonNullValues AS (
SELECT
COALESCE((SELECT MAX(ID) FROM footer WHERE CAR IS NOT NULL), 0) AS CAR_ID,
COALESCE((SELECT MAX(ID) FROM footer WHERE length IS NOT NULL), 0) AS LENGTH_ID,
COALESCE((SELECT MAX(ID) FROM footer WHERE width IS NOT NULL), 0) AS WIDTH_ID,
COALESCE((SELECT MAX(ID) FROM footer WHERE height IS NOT NULL), 0) AS HEIGHT_ID
FROM
footer
LIMIT 1
),
LastValues AS (
SELECT
(SELECT car FROM footer WHERE id = lnnv.car_id) AS car,
(SELECT length FROM footer WHERE id = lnnv.length_id) AS length,
(SELECT width FROM footer WHERE id = lnnv.width_id) AS width,
(SELECT height FROM footer WHERE id = lnnv.height_id) AS height
FROM
LastNonNullValues lnnv
)
SELECT * FROM LastValues;
what do you think about this one?

[@devrajpatidar5927]

nice problem statement and video thank you for such a great content
here is my approach to this problem :- microsoft sql server
with cte as (
SELECT *,
count(car) over(order by id asc) as car_n
,count(length) over(order by id asc) as length_n,
count(width) over(order by id asc) as width_n,
count(height) over(order by id asc) as height_n
FROM FOOTER
)
select top 1 min(car) over(partition by car_n) as car,
min(length) over(partition by length_n ) as length,
min(width) over(partition by width_n ) as width,
min(height) over(partition by height_n ) as height
from cte
order by id desc;

[@mohdtoufique3786]

Thanks for the content..My approach
Solution 1:- WITH lists AS(
SELECT '1' as helper,
string_agg(CAR,',') CAR_list,
string_agg(length,',') length_list,
string_agg(width,',') width_list,
string_agg(height,',') height_list
FROM FOOTER)
Select *,
right(CAR_list,CHARINDEX(',',REVERSE(CAR_list))-1) AS updated_car,
right(length_list,CHARINDEX(',',REVERSE(length_list))-1) AS updated_length,
right(width_list,CHARINDEX(',',REVERSE(width_list))-1) AS updated_width,
right(height_list,CHARINDEX(',',REVERSE(height_list))-1) AS updated_height
FROM lists
Solution 2:-WITH rn_columns AS(
SELECT *,COUNT(CAR)OVER(ORDER BY id) AS CAR_rn,
COUNT(LENGTH)OVER(ORDER BY id) AS length_rn,
COUNT(width)OVER(ORDER BY id) AS width_rn,
COUNT(height)OVER(ORDER BY id) AS height_rn
FROM FOOTER
)
SELECT TOP 1 first_value(car)OVER(PARTITION BY CAR_rn ORDER BY id) AS car_updated,
first_value(length)OVER(PARTITION BY length_rn ORDER BY id) AS lenth_updated,
first_value(width)OVER(PARTITION BY width_rn ORDER BY id) AS width_updated,
first_value(height)OVER(PARTITION BY height_rn ORDER BY id) AS height_updated
FROM rn_columns
ORDER BY id DESC

[@camomile4085]

I truly appreciate your extensive knowledge and attentive guidance. Thank you so much for your invaluable free lessons

[@zoltek11]

4 cte like this (select car from footer where car is not null and id = (select max(id) from footer where car is not null)) then select * from each cte

[@ContentRoomB]

Figured this one out using the first method. :) Looking forward to next problem now

[@prashantmhatre1328]

you can leverage first_value and last_value function -
with cte as
( SELECT *, max(id) over () max_id, last_value(length) ignore nulls over ( order by id) l , last_value(width) ignore nulls over ( order by id) w
, last_value(height) ignore nulls over ( order by id) h FROM FOOTER
)
select car ,l,w,h from cte
where id = max_id
;

[@janakmali8243]

We can leverage the coalesce and lag functions to get the last not null values for each columns.
My Solution using the sql sever:
with cte as (
SELECT f.id, f.car, coalesce(f.length, lag(length,2) Over (order by id )) as length,
coalesce(f.width, lag(f.width,1) Over (order by id )) as width
, f.height FROM FOOTER f
)
select * from cte
where id = (select max(id) from cte )

[@ArunYadav-s2o]

select substring_index(group_concat(car),',',-1), substring_index(group_concat(length),',',-1),substring_index(group_concat(width),',',-1) from footer

[@Zyrotech380]

Solution 1 is better than solution 2 , it's easy to understand.

[@vigneshvicky3481]

2nd approach s really aswsm and logically convincing too🎉

[@sammail96]

Very interesting. I like second approach

[@algawzy]

Many thanks for your efforts 👏👏💐💐💐

[@arthiques2577]

with cte as(
SELECT *,(case when length is null then 1 else 0 end)as ids,id-(case when length is null then 1 else 0 end)as gr1
FROM FOOTER)
,cte2 as(
select max(id)as id,max(car)as car,max(length)as length,max(width)as width,max(height)as height
from cte
group by gr1
),cte3 as (
select id,car,ifnull(length,lag(length)over())as length,ifnull(width,lag(width)over())as width,ifnull(height,lag(height)over())as height
from cte2
)
select *
from cte3
order by id desc
limit 1

[@SahilGupta-fr2vh]

Like both solutions, happy learning

[@Hsalz]

Neat!

[@nachikettare6404]

with cte1 as(
select *,lag(length,2) over() as length_prev_value,lag(width,1) over() as width_prev_value from Footer)
select id,car,length_prev_value as length,width_prev_value as width,height from cte1 order by id desc limit 1; this was my solution any feedback would be Appreciated.

[@sibir3878]

I guess , In case of lag, you are setting the offset as 2 in the length since you know that the 1st not null is in the 2nd previous row,but if it is not there the query will fail to return a not null value.

[@bassambenidir1729]

for the 1st solution you can do it without using CROSS JOINS:
SELECT (SELECT car
FROM footer
WHERE car IS NOT NULL
ORDER BY id DESC
LIMIT 1) AS last_car,
(SELECT length
FROM footer
WHERE length IS NOT NULL
ORDER BY id DESC
LIMIT 1) AS last_length,
(SELECT width
FROM footer
WHERE width IS NOT NULL
ORDER BY id DESC
LIMIT 1) AS last_width,
(SELECT height
FROM footer
WHERE height IS NOT NULL
ORDER BY id DESC
LIMIT 1) AS last_height;

[@HarleenKaur-c9p]

with cte1 as
(select car from footer where car is not null order by id desc limit 1),
cte2 as
(select length from footer where length is not null order by id desc limit 1 ),
cte3 as
(select width from footer where width is not null order by id desc limit 1 ),
cte4 as
(select height from footer where height is not null order by id desc limit 1 )
select car,length,width,height from cte1,cte2,cte3,cte4

[@fathimafarahna2633]

As always BEST

[@ArunYadav-s2o]

it can be easily done with help of group_concat + substring_index function in single line

[@oldskool99]

Didnt see any answers using stored procedures, so here is my solution. Its pretty similar to the first solution.
create procedure answer()
begin
declare c varchar(255);
declare l int;
declare w int;
declare h int;
select car into c from footer where id in (select max(id) from footer where car is not NULL);
select length into l from footer where id in (select max(id) from footer where length is not NULL);
select width into w from footer where id in (select max(id) from footer where width is not NULL);
select height into h from footer where id in (select max(id) from footer where height is not NULL);
select c as car,l as length,w as width,h as height;
end ;
call answer();

[@karangupta6530]

Like ur way of providing another solution through Stored procedure s well

[@Kirankumar-ml1ro]

select
(select car from footer where id in (select max(id) from footer where car is not NULL)),
(select length from footer where id in (select max(id) from footer where length is not NULL)),
(select width from footer where id in (select max(id) from footer where width is not NULL)),
(select height from footer where id in (select max(id) from footer where height is not NULL))

[@mohammedvahid5099]

Wel aspires bro

[@MojilShah]

how about this ?
select distinct first_value(car) over (order by case when car is null then 0 else 1 end desc,id desc) as car
, first_value(length) over (order by case when length is null then 0 else 1 end desc,id desc) as length
, first_value(width) over (order by case when width is null then 0 else 1 end desc,id desc) as width
, first_value(height) over (order by case when height is null then 0 else 1 end desc,id desc) as height
from FOOTER;

[@ckarkhan]

Most elegant solution!

[@vijaygupta7059]

I used your solution but used count function
with cte as
(
SELECT id
,car, count(car)over(order by (select null) rows between unbounded preceding and 0 following ) as car_rn
,length, count(length)over(order by (select null) rows between unbounded preceding and 0 following ) as length_rn
,width, count(width)over(order by (select null) rows between unbounded preceding and 0 following ) as width_rn
,height, count(height)over(order by (select null) rows between unbounded preceding and 0 following ) as height_rn
FROM FOOTER
)
Select top 1
FIRST_VALUE(car)over(partition by car_rn order by car_rn) as car_rn_value
,FIRST_VALUE(length)over(partition by length_rn order by length_rn) as length_rn_value
,FIRST_VALUE(width)over(partition by width_rn order by width_rn) as width_rn_value
,FIRST_VALUE(height)over(partition by height_rn order by height_rn) as height_rn_value
from cte
order by id DESC

[@gudiatoka]

Nice

[@rawat7203]

Thanks Sir

[@enisertem9738]

with base as (select id
, last_value(car) over (order by case when car is null then -1 else 1 * id end ASC rows between unbounded preceding and unbounded following) car
,last_value(length) over (order by case when length is null then -1 else 1 * id end ASC rows between unbounded preceding and unbounded following) length
,last_value(width) over (order by case when width is null then -1 else 1 * id end ASC rows between unbounded preceding and unbounded following) width
,last_value(height) over (order by case when height is null then -1 else 1 * id end ASC rows between unbounded preceding and unbounded following) height
from FOOTER)
select car,length,width,height from base join (select max(id) id from base ) b on base.id = b.id

[@bharathilifehacker7045]

How to learn create suitable table for project in mysql

[@ArghyadeepPal92]

My solution:
select * from
(select car from footer where id=(select max(id) from footer where car is not null))a
,(select length from footer where id=(select max(id) from footer where length is not null))b,
(select width from footer where id=(select max(id) from footer where width is not null))c,
(select height from footer where id=(select max(id) from footer where height is not null))d;

[@SevenSandy]

My solution by only using cte and joins
with cte as
(
SELECT *, case when car is null then 0 else 1 end as Car_flag,
case when length is null then 0 else 1 end as Length_flag,
case when Width is null then 0 else 1 end as width_flag,
case when height is null then 0 else 1 end as height_flag
from footer)
,c1 as
(
select car
from cte
where car_flag=1 and id=(select max(id) from cte where car_flag=1)
)
,c2 as
(select length
from cte
where length_flag=1 and id=(select max(id) from cte where length_flag=1)
)
,c3 as
(select width
from cte
where width_flag=1 and id=(select max(id) from cte where width_flag=1)
)
,c4 as
(select height
from cte
where height_flag=1 and id=(select max(id) from cte where height_flag=1)
)
select * from c1 join c2 join c3 join c4;

[@LuisHinojosaFalcon]

this solution is as fast as the solution with sums of partitions, but with less lines, worth mentioning:
WITH last_values AS (
SELECT
(SELECT car FROM FOOTER WHERE car IS NOT NULL ORDER BY ctid DESC LIMIT 1) AS car,
(SELECT length FROM FOOTER WHERE length IS NOT NULL ORDER BY ctid DESC LIMIT 1) AS length,
(SELECT width FROM FOOTER WHERE width IS NOT NULL ORDER BY ctid DESC LIMIT 1) AS width,
(SELECT height FROM FOOTER WHERE height IS NOT NULL ORDER BY ctid DESC LIMIT 1) AS height
)
SELECT *
FROM last_values;

[@invincible9971]

My solution:
select * from
(
with final_cte as
(
with cte as (
select id, car , case when car is not null then row_number() over (order by id) end as rnc,
length, case when length is not null then row_number() over (order by id) end as rnl,
width, case when width is not null then row_number() over (order by id) end as rnw,
height, case when height is not null then row_number() over (order by id) end as rnh
from FOOTER
)
select id, case when rnc in (select max(rnc) from cte) then car end as car,
case when rnl in (select max(rnl) from cte)then length end as length,
case when rnw in (select max(rnw) from cte) then width end width,
case when rnh in (select max(rnh) from cte) then height end height
from cte
)
select car, sum(length) over (order by id) as length, sum(width) over (order by id) as width, sum(height) over (order by id) as height from final_cte
) m
where car is not null and length is not null and width is not null and height is not null;

[@X-Agn]

i donot understand why sum would take previous value??

[@karankumarrayat6282]

With t1 as (Select *, SUM(LENGTH) OVER (ORDER BY ID ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS length_1, SUM(width) OVER (ORDER BY ID ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS width_1
FROM FOOTER)
Select car, length_1, width_1, height
from t1
where car ='Kia Sportage'

[@virensingh795]

select car,
case when length is null then (select length from footer where id = (select id from footer where length is not null order by id desc limit 1 )) else length end as length,
case when length is null then (select width from footer where id = (select id from footer where width is not null order by id desc limit 1 )) else width end as width,
height
from footer order by id desc limit 1

[@azizkapdawala8713]

Solution in MS SERVER:
SELECT TOP 1 CAR, (CASE WHEN LENGTH is NULL THEN (Select top 1 Length from cars where Length is not null order by id desc) ELSE LENGTH END) as LENGTH,
(CASE WHEN WIDTH is NULL THEN (Select top 1 width from cars where width is not null order by id desc) ELSE width END) as WIDTH,
(CASE WHEN HEIGHT is NULL THEN (Select top 1 height from cars where height is not null order by id desc) ELSE height END) as HEIGHT
FROM CARS
ORDER BY ID DESC

[@batuhancetin61]

thank you very much for the video. I tried to use "where" at the end of query instead of "limit" but I didn't come up with the same result. Could you please help me with this?

[@RahulThakur-fd4ri]

this series for fresher or experince

[@vikaskumar-hl3oj]

Can I know which types of business problems solved this query

[@sathvikdakappagari9122]

can this be solved using the lag function ?

[@dataengineeringsimplified1049]

with
cte1 as
(
SELECT top 1 car FROM FOOTER order by id desc
),
cte2 as
(
SELECT top 1 length FROM FOOTER where length is not null order by id desc
),
cte3 as
(
SELECT top 1 width FROM FOOTER where width is not null order by id desc
),
cte4 as
(
SELECT top 1 height FROM FOOTER where height is not null order by id desc
)
select (select car from cte1)as car,(select length from cte2) as length,(select width from cte3) as width,(select height from cte4) as height

[@algawzy]

Could you please provide a query that calculates working hours between two dates ( working hours start from 08:00 AM to 11:00 PM )

[@pathaktech9783]

Is it really useful use case?

[@radhikamaheshwari4835]

with cte as
(
SELECT
LAST_VALUE(car) ignore nulls over(order by id) as car,
LAST_VALUE(length) ignore nulls over(order by id) as length,
LAST_VALUE(width) ignore nulls over(order by id) as width,
last_value(height) ignore nulls over(order by id) as height,
row_number() over(order by id) as rn
FROM FOOTER
)
select * from cte where rn = (select max(rn) from cte)

[@Praveen_Kumar_R_CBE]

i feel lucky..

[@sourabhpatel3834]

with cte as (
select *,
sum(case when car is not null then 1 else 0 end) over(order by id) as car_rank,
sum(case when length is not null then 1 else 0 end) over(order by id) as lenght_rank,
sum(case when width is not null then 1 else 0 end) over(order by id) as width_rank,
sum(case when height is not null then 1 else 0 end) over(order by id) as height_rank
from footer)
select top 1 max(car) over(partition by car_rank order by id) as cars,
max(length) over(partition by lenght_rank order by id) as lengths,
max(width) over(partition by width_rank order by id) as widths,
max(height) over(partition by height_rank order by id) as heights
from cte
order by id desc

[@ritukumari1636]

This is my solution
SELECT REVERSE(SUBSTRING(REVERSE(string_agg(car,',')),1,CHARINDEX(',',REVERSE(string_agg(car,',')))-1)) as car
, REVERSE(SUBSTRING(REVERSE(string_agg(length,',')),1,CHARINDEX(',',REVERSE(string_agg(length,',')))-1)) as length
, REVERSE(SUBSTRING(REVERSE(string_agg(width,',')),1,CHARINDEX(',',REVERSE(string_agg(width,',')))-1)) as width
, REVERSE(SUBSTRING(REVERSE(string_agg(height,',')),1,CHARINDEX(',',REVERSE(string_agg(height,',')))-1)) as height
FROM FOOTER;

[@shivinmehta7368]

with cte as (

select car from footer where id in (
SELECT
max(id)
FROM FOOTER where car is not null
)
)
, cte1 as (
select length from footer where id in (
SELECT
max(id)
FROM FOOTER where length is not null
)
)
, cte2 as (
select width from footer where id in (
SELECT
max(id)
FROM FOOTER where width is not null
)

)
, cte3 as (
select height from footer where id in (
SELECT
max(id)
FROM FOOTER where height is not null
)
)
select * from cte
cross join cte1
cross join cte2
cross join cte3

[@Demomail-m6w]

Here, is my approach
select
split_part(listagg(car,','),',',-1) car,
split_part(listagg(length,','),',',-1) length,
split_part(listagg(width,','),',',-1) width,
split_part(listagg(height,','),',',-1) height from input;

[@bhavanig2582]

SOLUTION: 3
SELECT DISTINCT
FIRST_VALUE(CAR) OVER(ORDER BY ID DESC) CAR
,FIRST_VALUE(LENGTH) OVER(ORDER BY CASE WHEN LENGTH IS NOT NULL THEN 0 ELSE 1 END, ID DESC) LENGTH
,FIRST_VALUE(WIDTH) OVER(ORDER BY CASE WHEN WIDTH IS NOT NULL THEN 0 ELSE 1 END, ID DESC) WIDTH
,FIRST_VALUE(HEIGHT) OVER(ORDER BY ID DESC) HEIGHT
FROM FOOTER;

[@malcorub]

I use SQL Server but mine is similar to yours except I use last_value function.
SELECT DISTINCT
LAST_VALUE(CAR) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'Car'
,MIN([LENGTH]) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'Lenght'
,MAX([WIDTH]) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'Width'
,LAST_VALUE([HEIGHT]) OVER(ORDER BY ID ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 'Height'
FROM [D3_Footer]

[@malcorub]

Pretty clever use of the case statement on your part by the way. 😀

[@Satish_____Sharma]

Here is my easiest MYSQL solution:-
SELECT substring_index(group_concat(car),',',-1) as car,
substring_index(group_concat(length),',',-1) as length,
substring_index(group_concat(width),',',-1) as width,
substring_index(group_concat(height),',',-1) as height
FROM footer;

[@Pascald-jh8pw]

perfect

[@splendidabhi]

Completed day3 of #30DaySQLQueryChallenge

[@iswillia123]

Using Oracle, here's the query I came up with:
select * from (
SELECT
last_value(car ignore nulls) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) last_car,
last_value(length ignore nulls) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) last_length ,
last_value(width ignore nulls) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) last_width,
last_value(height ignore nulls) over (order by id ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) last_height
FROM FOOTER)
where rownum = 1
;

[@jesseantony1223]

my solution using CTE
with cte as
(select *, dense_rank() over(order by id) as l1_rank from FOOTER where length is not null),
cte1 as
(select *, dense_rank() over(order by id) as w1_rank from FOOTER where width is not null),
cte2 as
(select *, dense_rank() over(order by id) as h1_rank from FOOTER where height is not null),
cte3 as
(select a.id as new_id,a.length as new_length,a.width as new_width,a.height as new_height from cte a join cte1 b on a.id=b.id join cte2 c on a.id=c.id)
select id,q.car,new_length,new_width,new_height from FOOTER q cross join cte3 w where id in (select max(id) from FOOTER)

[@CebuProvince]

-- Solution 1: in sql awebwe
with cte as
(select *
, sum(case when car is null then 0 else 1 end) over(order by id) as car_segment
, sum(case when length is null then 0 else 1 end) over(order by id) as length_segment
, sum(case when width is null then 0 else 1 end) over(order by id) as width_segment
, sum(case when height is null then 0 else 1 end) over(order by id) as height_segment
from footer)
select top 1
first_value(car) over(partition by car_segment order by id) as new_car
, first_value(length) over(partition by length_segment order by id) as new_length
, first_value(width) over(partition by width_segment order by id) as new_width
, first_value(height) over(partition by height_segment order by id) as new_height
from cte
order by id desc
-- Solution 2:
select *
from (SELECT top 1 car FROM FOOTER where car is not null order by id desc ) car
cross join (SELECT top 1 length FROM FOOTER where length is not null order by id desc ) length
cross join (SELECT top 1 width FROM FOOTER where width is not null order by id desc ) width
cross join (SELECT top 1 height FROM FOOTER where height is not null order by id desc ) height;

[@tirthankarbagal4081]

can it be an alternative @techTFQ
select * from
(SELECT LAST_VALUE(car) OVER () as car FROM footer where car is not null limit 1) car
cross join(SELECT LAST_VALUE(length) OVER () as length FROM footer where length is not null limit 1) length
cross join(SELECT LAST_VALUE(width) OVER () as width FROM footer where width is not null limit 1) width
cross join(SELECT LAST_VALUE(height) OVER () as height FROM footer where height is not null limit 1) height;

[@ishanshubham8355]

Solution In Mysql with
cte as (
select 1 as v,group_concat(car) as car,group_concat(length) as length,group_concat(width) as width,group_concat(height) as height
from footer
group by v)
select substring_index(car,",",-1) as car,
substring_index(length,",",-1) as length,
substring_index(width,",",-1) as width,
substring_index(height,",",-1) as height
from cte

[@rohithr9122]

with cte as(
select *,COUNT(length)over(order by id)lcount,
COUNT(width)over(order by id)wcount,
COUNT(height)over(order by id)hcount
from FOOTER ),
cte2 as(
select id, car,first_value(length)over(partition by lcount order by lcount)Length,
first_value(width)over(partition by wcount order by wcount)Width,
first_value(height)over(partition by hcount order by hcount)Height
from cte)
select top 1 car,Length,Width,Height from cte2
order by Id desc

[@hariikrishnan]

Insert Queries:
drop table if exists cars ;
create table cars(
id int not null,
car varchar(100),
length int,
width int,
height int
)
insert into cars values
(1, 'Hyundai Tuscon', 15, 6, null ),
(2, null, null, null, 20 ),
(3, null, 12, 8, 15),
(4, 'Toyota Rav4', null, 15, null),
(5, 'Kia Sportage', null,null,18)

[@javwadmetavision]

with car as (select car,
row_number() over(order by id desc) as rn from footer
where car is not Null
),
length as (select length,
row_number() over(order by id desc) as rn from footer
where length is not Null
),
width as (select width,
row_number() over(order by id desc) as rn from footer
where width is not Null
),
height as (select height,
row_number() over(order by id desc) as rn from footer
where height is not Null
)
select
car.car,length.length,width.width,height.height
from car
inner join length on car.rn = length.rn
inner join width on length.rn = width.rn
inner join height on width.rn = height.rn
where car.rn = 1
Hi sir,I hope you are doing well,Thanks for awesome video.I wrote above query before watching the solution and I executed above query and It works.I feel glad.
Thanks a million sir :)

[@sriramyadlapalli4042]

select * from
(SELECT car FROM FOOTER where car is not null
limit 1 offset 2) car
cross join
(select length from FOOTER where length is not null
limit 1 offset 1) length
cross join
(select width from FOOTER where width is not null
limit 1 offset 2) width
cross join
(select height from FOOTER where height is not null
limit 1 offset 2) height

[@mattmatt245]

It's so much easier with power query. Just use fill down and keep the last row.

[@ayaalkasrawi826]

SELECT TOP 1
(SELECT TOP 1 car FROM FOOTER WHERE car IS NOT NULL ORDER BY ID DESC) AS car,
(SELECT TOP 1 length FROM FOOTER WHERE length IS NOT NULL ORDER BY ID DESC) AS length,
(SELECT TOP 1 width FROM FOOTER WHERE width IS NOT NULL ORDER BY ID DESC) AS width,
(SELECT TOP 1 height FROM FOOTER WHERE height IS NOT NULL ORDER BY ID DESC) AS height
FROM FOOTER;

[@Löwen_av]

SELECT TOP 1 car, (SELECT TOP 1 length FROM FOOTER where length is not null order by id desc) as length,
(SELECT TOP 1 width FROM FOOTER where width is not null order by id desc) as width,
(SELECT TOP 1 height FROM FOOTER where height is not null order by id desc) as height
FROM FOOTER
where car is not null
order by id desc

[@prannoymanas6987]

with cte as (
SELECT STRING_AGG(CONVERT(NVARCHAR(max), car), ';') as c_coll,
STRING_AGG(CONVERT(NVARCHAR(max), length), ';') as l_coll,
STRING_AGG(CONVERT(NVARCHAR(max), width), ';') as w_coll,
STRING_AGG(CONVERT(NVARCHAR(max), height), ';') as h_coll from FOOTER)


select substring(c_coll, 28, 39) as car,
substring(l_coll,4,5) as length,
substring(w_coll,5,6) as width,
substring(h_coll,7,8) as height
from cte;

[@SelvamB-es9ew]

MYSQL SOLUTION:
-- ===========================================================================
select
(select car from FOOTER where car is not null order by id desc limit 1) car,
(select length from FOOTER where length is not null order by id desc limit 1) length,
(select width from FOOTER where width is not null order by id desc limit 1) width,
(select height from FOOTER where height is not null order by id desc limit 1) height;
-- ===========================================================================

[@ARJUNKRISHNA-mq3wj]

select (SELECT car from footer where car is not null order by id desc limit 1) as c,(SELECT length from footer where length is not null order by id desc limit 1) as l,
(SELECT width from footer where width is not null order by id desc limit 1) as w,(SELECT height from footer where height is not null order by id desc limit 1) as h ; my solution

[@Aday_life]

select car, length, width, height from
(select car from (
select * from (
select id, car,
case when car is null then 0 else 1 end as car_part
from footer)
where car_part 0 order by id desc)
where rownum = 1) car_final
cross join (select length from (
select * from (
select id, length,
case when length is null then 0 else 1 end as length_part
from footer)
where length_part 0 order by id desc)
where rownum = 1) length_final
cross join (select width from (
select * from (
select id, width,
case when width is null then 0 else 1 end as width_part
from footer)
where width_part 0 order by id desc)
where rownum = 1) width_final
cross join (select height from (
select * from (
select id, height,
case when height is null then 0 else 1 end as height_part
from footer)
where height_part 0 order by id desc)
where rownum = 1) height_final

[@priyadarshinimadhusudanan6213]

Mysql: SELECT SUBSTRING_INDEX(GROUP_CONCAT(car), ',',-1) as car,
SUBSTRING_INDEX(GROUP_CONCAT(length), ',',-1) as length,
SUBSTRING_INDEX(GROUP_CONCAT(width), ',',-1) as width,
SUBSTRING_INDEX(GROUP_CONCAT(height), ',',-1) as height FROM FOOTER;

[@nambidasan6314]

;With car_CTE as
(
Select 'car' as keypair, car as value, id From Footer where car is not null
)
,car_footer_cte as
(
select keypair, value from car_CTE where id = (select max(id) from car_CTE)
)
,length_cte as
(
Select 'length' as keypair, length as value, id From Footer where length is not null
)
,length_footer_cte as
(
select keypair, value from length_cte where id = (select max(id) from length_cte)
)
,width_cte as
(
Select 'width' as keypair, width as value, id From Footer where width is not null
)
,width_footer_cte as
(
select keypair, value from width_cte where id = (select max(id) from width_cte)
)
,height_cte as
(
Select 'height' as keypair, height as value, id From Footer where height is not null
)
,height_footer_cte as
(
select keypair, value from height_cte where id = (select max(id) from height_cte)
)
,pivot_cte as
(
select keypair, cast(value as varchar(100)) value from car_footer_cte
union all
select keypair, cast(value as varchar(100)) value from length_footer_cte
union all
select keypair, cast(value as varchar(100)) value from width_footer_cte
union all
select keypair, cast(value as varchar(100)) value from height_footer_cte
)
SELECT car,length,width,height FROM
(SELECT keypair, value FROM pivot_cte )Tab1
PIVOT
(
MAX(value) FOR keypair IN (car,length,width,height)) AS Tab2

[@sourabhkumar6811]

with cte1 as
(
select * from
(select car,row_number()over(order by null)as rn from footer)A
where A.car is not null)
,cte2 as
(select * from
(select length,row_number()over(order by null)as rn1 from footer)A
where A.length is not null)
,cte3 as
(select * from
(select width,row_number()over(order by null)as rn2 from footer)A
where A.width is not null)
,cte4 as
(select * from
(select height,row_number()over(order by null)as rn3 from footer)A
where A.height is not null)
select cte1.car,cte2.length,cte3.width,cte4.height
from cte1
join
cte2
on 1=1
join
cte3
on 1=1
join
cte4
on 1=1
where cte1.rn=(select max(rn)from cte1)
and cte2.rn1=(select max(rn1)from cte2)
and cte3.rn2=(select max(rn2)from cte3)
and cte4.rn3=(select max(rn3)from cte4);
I hope the above query satisfies all the conditions.

[@vijayavenkatasatyaavinashk7760]

My Solution to it:
SELECT top 1
COALESCE(car, max(car) OVER (PARTITION BY car_maxid)) AS car ,
COALESCE(length, max(length) OVER (PARTITION BY length_maxid)) AS length,
COALESCE(width, max(width) OVER (PARTITION BY width_maxid)) AS width,
COALESCE(height, max(height) OVER (PARTITION BY height_maxid)) AS height
FROM (
SELECT *,
MAX(CASE WHEN length IS NOT NULL THEN id END) OVER (ORDER BY id) AS length_maxid,
MAX(CASE WHEN car IS NOT NULL THEN id END) OVER (ORDER BY id) AS car_maxid,
MAX(CASE WHEN width IS NOT NULL THEN id END) OVER (ORDER BY id) AS width_maxid,
MAX(CASE WHEN height IS NOT NULL THEN id END) OVER (ORDER BY id) AS height_maxid
FROM footer
) AS t order by id desc

[@raitup00]

Hi everyone

[@VijayKumar-bx2ov]

with cte as(
SELECT
STRING_AGG(length::text, ' ,') AS concatenated_lengths,
STRING_AGG(width::text, ' ,') AS concatenated_wid,
STRING_AGG(height::text, ' ,') AS concatenated_hei,
STRING_AGG(car::text, ' ,') AS concatenated_car
FROM FOOTER)
select
SPLIT_PART(concatenated_car, ',', -1) AS last_car,
SPLIT_PART(concatenated_lengths, ',', -1) AS last_len,
SPLIT_PART(concatenated_wid, ',', -1) AS last_wid,
SPLIT_PART(concatenated_hei, ',', -1) AS last_hei
from cte

[@rajkumarrajan8059]

WITH CARS AS
(
SELECT CAR FROM FOOTER
WHERE ID =
(
SELECT MAX (ID) FROM
(
SELECT ID FROM FOOTER
WHERE CAR IS NOT NULL
)CAR
)
) , LENGTH AS
(
SELECT LENGTH FROM FOOTER
WHERE ID =
(
SELECT MAX (ID) FROM
(
SELECT ID FROM FOOTER
WHERE LENGTH IS NOT NULL
)LT
)
), WIDTH AS
(
SELECT WIDTH FROM FOOTER
WHERE ID =
(
SELECT MAX (ID) FROM
(
SELECT ID FROM FOOTER
WHERE WIDTH IS NOT NULL
)WD
)
), HEIGHT AS
(
SELECT HEIGHT FROM FOOTER
WHERE ID =
(
SELECT MAX (ID) FROM
(
SELECT ID FROM FOOTER
WHERE HEIGHT IS NOT NULL
)HT
)
)
SELECT * FROM
CARS
JOIN LENGTH ON 1=1
JOIN WIDTH ON 1=1
JOIN HEIGHT ON 1=1

[@Kirankumar-ml1ro]

SELECT
(SELECT car FROM footer WHERE car IS NOT NULL ORDER BY id DESC LIMIT 1) AS car,
(SELECT length FROM footer WHERE length IS NOT NULL ORDER BY id DESC LIMIT 1) AS length,
(SELECT width FROM footer WHERE width IS NOT NULL ORDER BY id DESC LIMIT 1) AS width,
(SELECT height FROM footer WHERE height IS NOT NULL ORDER BY id DESC LIMIT 1) AS height;

[@gamingwithlegend3183]

Sir ek youtuber hai jo apke video ke topic ko copy karta hai.

[@ayaalkasrawi826]

WITH CTE_1 AS (
SELECT
ROW_NUMBER() OVER (ORDER BY car) AS rn,
(SELECT TOP 1 car FROM FOOTER WHERE car IS NOT NULL ORDER BY ID DESC) AS car,
(SELECT TOP 1 length FROM FOOTER WHERE length IS NOT NULL ORDER BY ID DESC) AS length,
(SELECT TOP 1 width FROM FOOTER WHERE width IS NOT NULL ORDER BY ID DESC) AS width,
(SELECT TOP 1 height FROM FOOTER WHERE height IS NOT NULL ORDER BY ID DESC) AS height
FROM FOOTER )
SELECT car, length, width, height
FROM CTE_1
WHERE rn= 1

[@user-gq6cg3ls7f]

with car as(
SELECT f.*, ROW_NUMBER() over (order by id) RN_C FROM FOOTER F where car is not null),
lengths as(
SELECT f.*, ROW_NUMBER() over (order by id) RN_L FROM FOOTER F where length is not null),
width as(
SELECT f.*, ROW_NUMBER() over (order by id) RN_W FROM FOOTER F where width is not null),
height as(
SELECT f.*, ROW_NUMBER() over (order by id) RN_H FROM FOOTER F where height is not null)
select c.car, l.length, w.width, h.height from car c, lengths l, width w, height h
where c.RN_C=3 and l.RN_L=2 and w.RN_W=3 and h.RN_H=3

[@Data_Vision326]

WITH t1 as
(SELECT id,
case when car is null then 'no car' else car END AS car,
CASE WHEN length is null then 0 else length end as new_length,
CASE WHEN width is null then 0 else width end as new_width,
CASE WHEN height is null then 0 else height end as new_height
from footer)
SELECT
(SELECT car from t1 order by id desc limit 1) as car,
(SELECT new_length from t1 WHERE new_length !=0 ORDER BY id DESC LIMIT 1 ) as new_length,
(SELECT new_width from t1 WHERE new_width !=0 ORDER BY id DESC LIMIT 1 ) as new_width,
(SELECT new_height from t1 WHERE new_height !=0 ORDER BY id DESC LIMIT 1 ) as new_height
from t1 LIMIT 1;

[@RituAdhiya]

WITH cte AS (
SELECT
(SELECT car FROM FOOTER WHERE car IS NOT NULL ORDER BY id DESC LIMIT 1) AS car,
(SELECT length FROM FOOTER WHERE length IS NOT NULL ORDER BY id DESC LIMIT 1) AS length,
(SELECT width FROM FOOTER WHERE width IS NOT NULL ORDER BY id DESC LIMIT 1) AS width,
(SELECT height FROM FOOTER WHERE height IS NOT NULL ORDER BY id DESC LIMIT 1) AS height
)
SELECT *
FROM cte;

[@Kirankumar-ml1ro]

SELECT
a.car AS car,
b.length AS length
FROM
(SELECT car, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn FROM footer WHERE car IS NOT NULL limit 1) AS a
JOIN
(SELECT length, ROW_NUMBER() OVER (ORDER BY id DESC) AS rn FROM footer WHERE length IS NOT NULL limit 1) AS b
ON
a.rn = b.rn;

[@nadimatlanagesh1951]

WITH ctr AS(
SELECT
listagg(car,',') WITHIN GROUP (ORDER BY id ) CAR_list,
listagg(length,',') WITHIN GROUP (ORDER BY id ) length_list,
listagg(width,',') WITHIN GROUP (ORDER BY id ) width_list,
listagg(height,',') WITHIN GROUP (ORDER BY id ) height_list
FROM FOOTER)
Select
right(CAR_list,CHARINDEX(',',REVERSE(CAR_list))-1) AS car,
right(length_list,CHARINDEX(',',REVERSE(length_list))-1) AS length,
right(width_list,CHARINDEX(',',REVERSE(width_list))-1) AS width,
right(height_list,CHARINDEX(',',REVERSE(height_list))-1) AS height
FROM ctr;

[@gokulsutar1696]

WITH CTE AS (
SELECT * FROM
(SELECT CAR FROM FOOTER WHERE CAR IS NOT NULL ORDER BY ID DESC ) CAR
CROSS JOIN (SELECT LENGTH FROM FOOTER WHERE LENGTH IS NOT NULL ORDER BY ID DESC )LENGTH
CROSS JOIN (SELECT WIDTH FROM FOOTER WHERE WIDTH IS NOT NULL ORDER BY ID DESC )WIDTH
CROSS JOIN (SELECT HEIGHT FROM FOOTER WHERE HEIGHT IS NOT NULL ORDER BY ID DESC ) HEIGHT
CROSS JOIN (SELECT ID FROM FOOTER WHERE ID IS NOT NULL ORDER BY ID DESC )ID
)

SELECT CAR,LENGTH,WIDTH,HEIGHT FROM
(SELECT E.*,ROW_NUMBER()OVER(ORDER BY ID DESC) AS RN
FROM CTE E)
WHERE RN =1;