In the first approach without doing group by and all,we can just apply distinct after greatest and and least things

Hi Rishabh, I was trying with multiple methods and i found if we use the query like select * from Travel where source > destination; the answer would be the same and this is the shortest method. though i m not sure if there is any discrepancy with this query, Please clarify or suggest.

Mishra ji 🙏, you are wonderful teacher and it is reflected in your videos. 🙂 It is also evident from your prof. experience.

@RishabhMishraOfficial

Жыл бұрын

Glad you liked ✅️

here we can use mod function mod(id,2)=0 actully we can solve many methods but sir you solved very easy way great

I go with inner join. Method 2. Method 3 slightly difficult.

Sir, I enjoyed this session. Great explanation. I learnt SQL from a course recommended by you in one of your videos. SQL Go from Zero to hero by Jose Portilla. Few concepts were not covered in that course, such as windows functions, greatest, least etc. Can you please tell where can I also learn such concepts?

@RishabhMishraOfficial

Жыл бұрын

You can watch my video on SQL, it has all the topics u mentioned

create table travel ( source varchar(50), destination varchar(50), distance int); insert into travel (source, destination, distance) values ('Mumbai', 'Bangalore', 500), ('Bangalore','Mumbai', 500), ('Delhi', 'Mathura', 150), ( 'Mathura','Delhi', 150), ('Nagpur', 'Pune', 500), ( 'Pune','Nagpur', 500);

@user-kk8xf1jc8h

Ай бұрын

should have checked comments before starting the video lol

Please find the code to create the mentioned table in this tutorial: create table travel ( source varchar(50), destination varchar(50), distance varchar(50)); insert into travel (source, destination, distance) values ('Mumbai', 'Bangalore', 500), ('Bangalore','Mumbai', 500), ('Delhi', 'Mathura', 150), ( 'Mathura','Delhi', 150), ('Nagpur', 'Pune', 500), ( 'Pune','Nagpur', 500);

@RishabhMishraOfficial

Жыл бұрын

thanks for sharing :)

@jayveersingh9803

4 ай бұрын

thnx alot man....how these greatest is working and giving output i got confused in that...u saved me

Sir in using self join I am getting only 2 rows of output but as you doing in your slide it's having 3 rows of data

using cte : with cte1 as (select source, row_number() over(order by (select null)) as rn_s from travel ) select t1.source, t1.destination, t1.distance from travel as t1 join ( select c1.source as source, c2.source as destination from cte1 c1 join cte1 c2 where c1.source c2.source and c1.rn_s ) as t2 on t1.source = t2.source and t1.destination = t2.destination ;

If you give the link of the tables in description which you used to solved the problems in this video then it will be better rishabh

hi, love your teaching technique, please make a video on advance power bi interview Q&A. it will really appreciated. thankyou

@RishabhMishraOfficial

9 ай бұрын

Today uploaded a video on advanced power bi DAX

Using greatest and least method select greatest(source,destination),least(source,destination),max(distance) from travel group by greatest(source,destination),least(source,destination);

My personal two methods, M1: select distance,GROUP_CONCAT(source) from (select distinct * from table) as sub_query GROUP BY distance; (:: "select distinct * from table" important if you have original duplicates cities pair) M2: select DISTINCT greatest(source,destination) as "from",least(source,destination) as "to",distance from table;

Hello Rishabh, Thanks for creating such simple explanation for complex problems, Your approach was really easy to understand. I have seen the same question on other youtube channels but it was quite difficult for me to understand the same problem.

@RishabhMishraOfficial

Ай бұрын

Glad it's helpful ✅️

great and excellence sir

@RishabhMishraOfficial

Жыл бұрын

Glad you liked ✅️

Bhaiya direct bhi ho skta h na ye to Select * from travel Where source>deatination; Aise

@tanishqtomar9786

17 күн бұрын

usme last wala source thdi aayega kyuki p>n hta h na shyd

Hello, could you please help on min vs least ,while doing least are we need partion on table ?

Do you have in English version?

please comeup with more interview questions (sql) that would be helpful. Thank you

@RishabhMishraOfficial

9 ай бұрын

Already uploaded 4 videos on sql interview questions and answers

why not just use select statement SELECT * FROM travel WHERE source > destination

Sir, Could u pls tell me in Sub query what is the use of NOT EXISTS Function.....what will this not exists function suppose to do here?pls help

platform to practise advance sql??

I have a query, there are multiple data sciences projects available in KZread can you please tell me if I can inspire by those projects and make similar projects for my project portfolio, please reply me so that I can start building my project portfolio

Why u use not exist in subquery method?

Sir, Is there any chance of connecting with you over a call?

Being from non IT background, If i learn SQL and other required things how i get job ???

@Mr.tejaspatil96k

Ай бұрын

Are bhai jo hain unhe nhi lag rahi tum kaha bhid badha rahe ho😂

@whyme_141

Ай бұрын

U can get, ignore these Auswholes, learn basics from KZread and than Start getting certification from Microsoft or Google. If u get these Certificates, u can easily switch

Can't understand why the max function is used with distance? We are not using distance in the group by then why is it throwing in an error to add an aggregate function. Is it necessary to add all the called variables while using the group by on any one of them?🤔 Also, why the *where not exists* is added in third method? ThanQ in advance. @rishabh mishra gr8 going...🖖

@EnglishwithAjayraj

Ай бұрын

When we use GROUP BY, it's a must to use an aggregate function. That's why MAX is used here.

Hi, sir aap data ka file post kr diya kijiye so practice kr sake hmlog

@RishabhMishraOfficial

Жыл бұрын

Hi, it's a simple data set with 3 columns. You can prepare on your own 😄

@Amann7040

Жыл бұрын

@@RishabhMishraOfficial ok sir

Good work bro keep it up. My simple approach Select * from (Select *,ROW_NUMBER()over( order by distance) as number from travel) n where n.number % 2=1

I am sorry but I must say beginners will find it hard to understand your Queries In your 3rd method beginners will gona ask why is there NOT EXISTS clause. Even after watching your whole SQL playlist 😅

@RishabhMishraOfficial

Жыл бұрын

Is it, okay next time will make it more understandable ✅️

sirji third method mein agar dono city same alphabet se start hogi to appka favorite method fail kr jayega (iss table mein it worked) lol!

@RishabhMishraOfficial

Жыл бұрын

Yess issiliye 3 methods share kiya hai.. and already mentioned in the video that 3rd method is specifically gor this problem. Watch video properly 🤦‍♂️

@SyedHaidy

10 ай бұрын

Comparison operator doesn't just compare first letter. It works by comparing lexicographically. For example: Mumbai In this case, first letter is equal but in the second letter, "u" comes before "v" so u < v and hence the result Mumbai < Mvmbai.

Sir I am using 1st and 2nd method in Microsoft SQL server and it is showing error. In 2nd method it is saying 'the row_number must have an over clause with order by'. And in 1st method it is showing ' greatest ' is not a recognised built-in function name. Please help me

@shubhamswami7275

10 күн бұрын

He is using postgre SQL, in MS SQL it doesn't support least and greatest commands. And secondly, u need to specify an order by clause in MS SQL Use order by source, destination it will work.

first method was the easiest...other 2 I did not understand😅

@RishabhMishraOfficial

Жыл бұрын

Awesome 👌

select * from travel where source>destination; is this query right or wrong it show the same result can anyone please explain this query how this is working ????

@SyedHaidy

10 ай бұрын

It's nice, I also came up with it but it won't work if we change the question a little by adding a single entry with no reversed entry, Method 2 will also fail in that situation.

I solved it this way: SELECT * FROM travel WHERE source IN ('Mumbai','Mathura','Nagpur') is it right ??...coz it is giving the same results

@govindshah6037

Жыл бұрын

This is not correct because if the data is huge then how you gonna get this list ('Mumbai','Mathura','Nagpur')...?

@SayedReyaz

Жыл бұрын

Hardcoding Values which is Updateble or replacable in any way is not recommended in any programming language. Always Remember

sir group by distance kr denge to ho jaega

@RishabhMishraOfficial

Жыл бұрын

Share the sql query here

why not just SELECT * FROM travel WHERE source > destination, also only exists keyword in place of not exists can be used...

Sir, u have given 3 method bt we can simply write it as follows - Select * from travel Where source IN ( Mumbai, mathura, nagpur); Plz tell me if it is correct?

@gauravsingh-gn4zz

11 ай бұрын

What if there is n no. Of city. And you don't know the names .. will you pass n no. Of city.

table download link description mai nhii diya sir?

@RishabhMishraOfficial

Жыл бұрын

Query is there just copy paste n you will get the same table created

using sub query ,, inside query is giving the same results, then why we need to use outer not exists

@RishabhMishraOfficial

9 ай бұрын

If it works then u can use it 😉

I don't understand the leaste and greatest concept of text not number only text how it work?

@SyedHaidy

10 ай бұрын

It compares a string lexicographically. For example: "Mumbai In this case, first letter is equal but in the second letter, "u" comes before "v" so u Now use this concept in GREATEST and LEAST. If we find GREATEST(Mumbai, Mvmbai), it will give us "Mvmbai".

with Travel as (select * ,row_number() over() as SrNo from Table_Name) select * from Travel where SrNo%2=0 another Easiest Solution

SELECT source,destination,distance FROM travel WHERE source LIKE 'M%' OR source LIKE 'N%';

@eeshangautam

9 ай бұрын

But it will fail as soon as the use case is changed.

Hi sir, i am not getting the exact output

@RishabhMishraOfficial

10 ай бұрын

Check the join condition and try again

code created below. create table Travel ( source varchar(20), destination varchar(20), distance int8 ); insert into travel (source,destination,distance) values ('Mumbai','Bnagalore',500), ('bangalore','Mumbai',500), ('delhi','mathura',150), ('mathura','delhi',150), ('nagpur','pune',500), ('pune','nagpur',500);

@kumarlalit1840

Жыл бұрын

ThanQ use this insert into create table Travel ( source varchar(20), destination varchar(20), distance int8 ); insert into travel (source,destination,distance) values ('mumbai','bangalore',500), ('bangalore','mumbai',500), ('delhi','mathura',150), ('mathura','delhi',150), ('nagpur','pune',500), ('pune','nagpur',500); I hope this helps 😊

Method 2 Will not work If there is duplicate value in either source or destination please have a thought on that e.g You can add a new record like ('Mumbai', 'Goa', 700) & ('Goa', 'Mumbai', 700) then try this method-2 and it will not work

@arupchandra8602

Жыл бұрын

Then another condition need to add t1.Source = t2.destination AND t1.destination = t2.source AND t1.source > t2.source

@SyedHaidy

10 ай бұрын

It will still work, because we are checking both Source and Destination by that AND statement. t1.source = t2.destination AND t1.destination = t2.source -------------------------------------------------------- The situation where Method 2 will fail is if we add a single entry with no reversed entry.

i found out the easiest one select source, destination, distance FROM travel WHERE length(source) between 6 and 7

@superrv0023

5 ай бұрын

this method is not dynamic. If a new value comes up then the code needs to be changed

In MySQL, it is working even without Group By Clause using Greatest and Least functions. Any idea how its working

Explanation should be improved...

@RishabhMishraOfficial

Ай бұрын

Okayy Raman bhai, will work on it 🙏

I m failed to do also understand

I have found a normal easy way where we can just filter with distance. SELECT C1.SOURCEID, C1.SOURCE, C1.DESTINATION , C1.DISTANCE FROM CITY C1 INNER JOIN CITY C2 ON C1.DISTANCE = C2.DISTANCE WHERE C1.SOURCEID < C2.SOURCEID ;