This series is proving to be extremely useful for me, thanks a lot for creating this SQL playlist. If possible, can you please include some practice questions as well?
the idea is the ORDER BY should be a part of the SELECT statement as a final output as DISTINCT . Example 1 : SELECT Distinct A,B From T1 Order By B DESC ==>will work SELECT DISTINCT A from T1 ORDER BY B ==> will not work . Example 2 : SELECT DISTINCT A from t1 ORDER BY B; Steps: 1. SELECT A,B from t1; ==> X 2. SELECT DISTINCT A,B from X ==>Y (Not the distinct clause I wanted to fire) 3. SELECT A FROM Y ORDER BY B; ==> Final Answer which is Wrong MySQL prevents it
i've created a new data and ruuning this below query. SELECT DISTINCT LOCATION FROM STUDENTS ORDER BY ID; it's giving me result in mysql without any errors.
It is not running for me -- create CREATE TABLE EMPLOYEE ( empId INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL ); -- insert INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales'); INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting'); INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales'); -- fetch select distinct name from EMPLOYEE ORDER BY dept ; Returns ERR ==> ERROR 3065 (HY000) at line 15: Expression #1 of ORDER BY clause is not in SELECT list, references column 'db_3ykqa8mh7_3ymnahrru.EMPLOYEE.dept' which is not in SELECT list; this is incompatible with DISTINCT Refer MySQL Manual (search with 3065 you'll get the error message details): dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_field_in_order_not_select
I think you pointed it out correctly, even the video was unclear about it. I don't know why other people are able to run it properly without errors. But yes if the column used for ordering is also used in select then it should work without error (although not give desired output).
I don't know how the below query is working for me select DISTINCT source_of_joining from students ORDER BY enrollment_date DESC LIMIT 5; Could you please explain?
For me its working ... mysql> select distinct(source_of_joining) from students order by enrollment_date desc; +-------------------+ | source_of_joining | +-------------------+ | linkedin | +-------------------+ 1 row in set (0.00 sec)
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query: set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; and then try. Please ensure the order should be the same as above. You can verify if any of the SQL modes are missing using: SELECT @@sql_mode ;
hi sir the command :- select distinct source_of_joining from students order by enrollment_date; is working without any issue in mariadb it not show any error and i am getting results
check sql_mode set. For MYSQL, below worked for me: Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query: set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; and then try. Please ensure the order should be the same as above. You can verify if any of the SQL modes are missing using: SELECT @@sql_mode
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query: set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; and then try. Please ensure the order should be the same as above. You can verify if any of the SQL modes are missing using: SELECT @@sql_mode
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query: set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; and then try. Please ensure the order should be the same as above. You can verify if any of the SQL modes are missing using: SELECT @@sql_mode
SEELCT DISTINCT SOURCE_OF_JOINING FROM STUDENT ORDER BY ENROLL_DATE DESC; This query is supposed to fail but why is it giving DISTINCT source_of_joining as result??
I am not sure if you have explained it, right. I understand the order of execution is FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP
Checkout the Big Data course details here: trendytech.in/?referrer=youtube_sql8
Excellect video sir, no body thought me this point in any of the youtube videos which I had watched till now.. hats off to you sir
I am glad that you are liking teaching style :)
This series is proving to be extremely useful for me, thanks a lot for creating this SQL playlist.
If possible, can you please include some practice questions as well?
This series is going to be a life changing for ETL developers.
I am happy to be hearing such good responses for this series :)
thanx for explanation sir love your lectures till now you are teaching according to what you mentioned in first lecture🙏🙏
Thank you so much for wonderful explanation . This motivates me to learn more.
Great explanation sir🔥🔥🔥
I am binge watching, everything is so clear, thank you so much !!!
amazing vedio... very depth understanding
Fantastic lectures!
If the number of rows at 12:20 time same as in both the distinct queries then will it work?
Excellent deep dive into concepts, Hats off to you
I have one question
If we run this query then it should be work or not
SELECT DISTINCT source _of_joining FROM students ORDER BY source_of_joining ;
awesome .. loving it..
Nice videos
Please make a few videos on practical scenarios.
Very nicely explained, this was very intricate. Thank you :)
Excellent work sir, would love to see something in python if you can.Thank you sir.
Hi sir,
Can you please tell me where I can find this notes
so what is the solution to get the output of that perticular question if we can't fire that query what should be the query to get that output?
select distinct source_of_joining from students order by enrollment_date desc limit 3; this query works in db browser for sqllite
Great video SIr!
Sir, what is the alternative to "Distinct and Order By" clause to get the correct output in this situation?
Using group by and order by.
Also, the output would not make sense as enrolment_date is set to TIMESTAMP. So technically every record will have a DISTINCT date/value
Thank you sir
All the very best
the idea is the ORDER BY should be a part of the SELECT statement as a final output as DISTINCT .
Example 1 :
SELECT Distinct A,B From T1 Order By B DESC ==>will work
SELECT DISTINCT A from T1 ORDER BY B ==> will not work .
Example 2 :
SELECT DISTINCT A from t1 ORDER BY B;
Steps:
1. SELECT A,B from t1; ==> X
2. SELECT DISTINCT A,B from X ==>Y (Not the distinct clause I wanted to fire)
3. SELECT A FROM Y ORDER BY B; ==> Final Answer which is Wrong MySQL prevents it
i've created a new data and ruuning this below query.
SELECT DISTINCT LOCATION FROM STUDENTS ORDER BY ID;
it's giving me result in mysql without any errors.
It is not running for me
-- create
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');
-- fetch
select distinct name from EMPLOYEE ORDER BY dept ;
Returns ERR ==> ERROR 3065 (HY000) at line 15: Expression #1 of ORDER BY clause is not in SELECT list, references column 'db_3ykqa8mh7_3ymnahrru.EMPLOYEE.dept' which is not in SELECT list; this is incompatible with DISTINCT
Refer MySQL Manual (search with 3065 you'll get the error message details): dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html#error_er_field_in_order_not_select
I think you pointed it out correctly, even the video was unclear about it. I don't know why other people are able to run it properly without errors. But yes if the column used for ordering is also used in select then it should work without error (although not give desired output).
Sir if WHERE clause is also used in query then what will be the order of execution? In this case also, FROM will be processed first?
I will cover that in upcoming sessions
I don't know how the below query is working for me
select DISTINCT source_of_joining from students
ORDER BY enrollment_date DESC
LIMIT 5;
Could you please explain?
In this case we can use group by and then order by to sort them . Please clear this doubt sir. How to sort them??
This will be covered in the upcoming sessions
For me its working ...
mysql> select distinct(source_of_joining) from students order by enrollment_date desc;
+-------------------+
| source_of_joining |
+-------------------+
| linkedin |
+-------------------+
1 row in set (0.00 sec)
Even for me also it is working. Can anyone clarify what is it we are missing?
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
and then try. Please ensure the order should be the same as above.
You can verify if any of the SQL modes are missing using:
SELECT @@sql_mode ;
why do you need to change sql mode set when it's working fine.
@@pallavimanan4124 STILL SHOWING ERROR.. WHEN I COPY AND PASTE IN MY WORKBENCH
Why this query is executing in XAMPP without any error?
hi sir the command :-
select distinct source_of_joining from students order by enrollment_date;
is working without any issue in mariadb it not show any error and i am getting results
Keshav karki
1 second ago
But try with diff diff example it will give wrong output
mysql> select * from students;
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| student_id | first_name | last_name | middle_name | course_selected | student_email | student_phone | location |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| 1 | yack | sharma | NULL | 2 | rohit@gmail.com | 9191919191 | bangalore |
| 2 | virat | kohli | NULL | 1 | virat@gmail.com | 9292929292 | hyderabad |
| 3 | shikhar | dhawan | NULL | 3 | shikhar@gmail.com | 9292929293 | bangalore |
| 4 | rahul | dravstudent_id | NULL | 1 | rahul@gmail.com | 9494949494 | chennai |
| 5 | keshav | karki | NULL | 4 | kk@gamil.com | 9619954696 | mumbai |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
5 rows in set (0.00 sec)
mysql> select distinct location from students order by first_name;
+-----------+
| location |
+-----------+
| mumbai |
| chennai |
| hyderabad |
| bangalore |
+-----------+
It should have given in ordr mumbai>chennai>>bangalore>hydrabad
check sql_mode set. For MYSQL, below worked for me:
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
and then try. Please ensure the order should be the same as above.
You can verify if any of the SQL modes are missing using:
SELECT @@sql_mode
Even i'm not getting any error in mysql while running that query.
Sir if we do order by on source_of_joining then would it work ?
Hi did you get the answer?
hi sir I tried this with one ide but while I tried this it showed the result
Sir seems like in MYSQL this will work
yeah its working for me as well (distinct and order by together) .
This depends on the final output. I explained in the comments. I was also confused a bit for this
Keshav karki
1 second ago
But try with diff diff example it will give wrong output
mysql> select * from students;
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| student_id | first_name | last_name | middle_name | course_selected | student_email | student_phone | location |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| 1 | yack | sharma | NULL | 2 | rohit@gmail.com | 9191919191 | bangalore |
| 2 | virat | kohli | NULL | 1 | virat@gmail.com | 9292929292 | hyderabad |
| 3 | shikhar | dhawan | NULL | 3 | shikhar@gmail.com | 9292929293 | bangalore |
| 4 | rahul | dravstudent_id | NULL | 1 | rahul@gmail.com | 9494949494 | chennai |
| 5 | keshav | karki | NULL | 4 | kk@gamil.com | 9619954696 | mumbai |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
5 rows in set (0.00 sec)
mysql> select distinct location from students order by first_name;
+-----------+
| location |
+-----------+
| mumbai |
| chennai |
| hyderabad |
| bangalore |
+-----------+
It should have given in ordr mumbai>chennai>>bangalore>hydrabad
But Code, select distinct source_of_joining from students order by enrollment_date; is working on my
my sql workbench...What is the possible reason?
Working for me too
But try with diff diff example it will give wrong output
mysql> select * from students;
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| student_id | first_name | last_name | middle_name | course_selected | student_email | student_phone | location |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
| 1 | yack | sharma | NULL | 2 | rohit@gmail.com | 9191919191 | bangalore |
| 2 | virat | kohli | NULL | 1 | virat@gmail.com | 9292929292 | hyderabad |
| 3 | shikhar | dhawan | NULL | 3 | shikhar@gmail.com | 9292929293 | bangalore |
| 4 | rahul | dravstudent_id | NULL | 1 | rahul@gmail.com | 9494949494 | chennai |
| 5 | keshav | karki | NULL | 4 | kk@gamil.com | 9619954696 | mumbai |
+------------+------------+----------------+-------------+-----------------+-------------------+---------------+-----------+
5 rows in set (0.00 sec)
mysql> select distinct location from students order by first_name;
+-----------+
| location |
+-----------+
| mumbai |
| chennai |
| hyderabad |
| bangalore |
+-----------+
It should have given in ordr mumbai>chennai>>bangalore>hydrabad
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
and then try. Please ensure the order should be the same as above.
You can verify if any of the SQL modes are missing using:
SELECT @@sql_mode
Above query working in the version Mysql 8.0.31
Update your sql_mode by using the below query and it will behave normally and throw error on the erroneous query:
set sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
and then try. Please ensure the order should be the same as above.
You can verify if any of the SQL modes are missing using:
SELECT @@sql_mode
First person completed the course 😅
SEELCT DISTINCT SOURCE_OF_JOINING FROM STUDENT ORDER BY ENROLL_DATE DESC;
This query is supposed to fail but why is it giving DISTINCT source_of_joining as result??
Sir hindi mein start kijiye.please asap🙏
Definitely. Very soon
I am not sure if you have explained it, right. I understand the order of execution is
FROM
ON
JOIN
WHERE
GROUP BY
WITH CUBE or WITH ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
Thanks Sir