Ascendion SQL Interview Question - Find Largest value

[@adityatribhuvan7870]

select col1,max_value from
(select *,
GREATEST(col2,col3) as max_value
from tbl_maxval) as a

[@AshutoshKumar-uz2om]

Hi.. your sql inter view question really helping many to land in their dream job. we can also achive the output as below. It's working in mysql
with cte as (
select col1, col2
from tbl_maxval
union all
select col1, col3
from tbl_maxval
)
select col1, max(col2) as maxval
from cte
group by col1;

[@CloudChallengers]

@AshutoshKumar-uz2om, Thanks for the encouragement.

[@adityatribhuvan7870]

with cte as
(select*, greatest(col2,col3) as maxval
from tbl_maxval)
select col1, maxval from cte
i used this approach

[@chandanpatra1053]

I have a question to try
Q1:
id
1
2
3
4
Output
id
1
2
2
3
3
3
4
4
4
4
Q1 : 1 is repeated 1 time,2 two times, 3 three times,4 four times
kindly solve this question in a very easy manner. Also make some other variants of this question like
Q2: id
1
5
7
8
9
13
Output
2
3
4
6
10
11
12
Q2 : Find the missing no from the id column.
Hope you will solve and make a video these 2 questions.
TIA

[@khadijasultana8964]

Q1:select t1.id as i,t2.id as j from
list t1 join list t2
where t1.id>=t2.id
order by i,j;
-- i gives you output column
-- assuming table name list
with column "id"

[@khadijasultana8964]

Q2
-- generate list of numbers from 1 to max in the table 'list'
with recursive cte as(
select 1 as n from list
union all
select n+1 from cte
where n

[@CloudChallengers]

Sure Chandan. Keep posting many such scenario based interview questions

[@chandanpatra1053]

@@khadijasultana8964 this query fails if the id is in random order.I mean we will not get the desired output as asked. lets say
id
1
3
6
10. then the query you have mentioned doesn't gives output as 1 one time,3 three times,6 six times,10 ten times. The query should be generalised. It should pass every Test Cases.

[@maheshnagisetty4485]

Q1.WITH CTE AS (
SELECT id, 1 AS ccount
FROM tab1
UNION ALL
SELECT id, ccount + 1
FROM CTE
WHERE ccount < id
)
SELECT id
FROM CTE
ORDER BY id, ccount;

[@khadijasultana8964]

select col1,if(col2>=col3,col2,col3) as max_value from tbl

[@CloudChallengers]

Thanks for posting very simple query Khadija. Function should be IIF( )

[@khadijasultana8964]

@CloudChallengers I use mysql, if() works .
Can you pls explain iif() ?

[@CloudChallengers]

@@khadijasultana8964 got it. We should use IIF( ) in Microsoft SQL, I was saying that.

[@prajju8114]

with cte_max as
(
Select col1,case when col2 > col3 then col2 when col3 > col2 then col3 else 0 end max_val from tbl_maxval
)
Select col1,max_val from cte_max
we can use this query

[@hairavyadav6579]

Great question :::
My approach please let me know this will work or not
with cte as (select *,case when col2> col3 then col2 else col3 end as max from tbl_maxval)
select col1,max as maxval from cte;

[@UnrealAdi]

with cte as (
select col1, col2
from tbl_maxval
union
select col1, col3
from tbl_maxval
order by 1
)
select col1, max(col2) maxval
from cte
group by 1
;

[@soumyareddy7995]

Hi sir... on campus interview was done for me recently I got documents submission mail I have submitted the all required docs but till today no update regrading my offer letter and joining..
Some of my college mates got immediately joining letter after submitting documents.
Please explain me what's going bruh

[@CloudChallengers]

@@soumyareddy7995, Don't worry, it's normal for the process to take longer for some. If you haven't heard back in a few more days, send a follow-up email to HR asking for an update.
Hope that helps

[@jhonsen9842]

select col1,col3 as maxval from tbl_maxval where col3 > col2
union all
select col1,col2 as maxval from tbl_maxval where col2 > col3

[@CloudChallengers]

Yes Jhonsen. Thanks for posting different approach.

[@sravankumar1767]

We have a input table s.no 1, 2,3 and category level 1, level2, level3 i want output as level1 first record , level 1, level2 2nd record, level 1, level2, level3 3rd record can u explain me

[@sravankumar1767]

Can u pls explain how can we write a sql query

[@CloudChallengers]

@@sravankumar1767 Thanks for posting the question Sravan.
You can refer the below query.
create table categories (sno int, category nvarchar(50))
insert into categories values (1,'level1'), (1,'level2'), (1,'level3')
select
(select STRING_AGG(t2.category, ',') WITHIN GROUP (ORDER BY t2.category)
from categories t2
where t2.sno = t1.sno and t2.category

[@sravankumar1767]

@@CloudChallengers Thank you very much

[@suldn2113]

is this fresher on campus interview question ?

[@CloudChallengers]

@suldn2113, This question is asked in Data Analytics interview for experienced candidate with 3+ yrs of experience in SQL.

[@sravankumar1767]

Nice explanation 👌 👍 👏

[@saiteja-gb8ho]

SELECT COL1, CASE WHEN COL3>COL2 THEN COL3 ELSE COL2 END AS MAXVAL

FROM tbl_maxval

[@CloudChallengers]

Yes Saiteja. Thanks for posting alternative approach