--.Finding duplicates records select emp_name, count(*) from employee group by emp_name HAVING count(*) > 1 -- Scenario: If the Table contains multiple columns where only emp_name is duplicate, but rest of the values are unique. --Using Windows function select * from ( select emp_name, ROW_NUMBER() OVER(PARTITION by emp_name) rn from Employee) where rn > 1
Thank you. I would like to know the query for below question pls: Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY),CITY LIMIT 1; SELECT CITY,LENGTH(CITY) FROM STATION ORDER BY LENGTH(CITY) DESC,CITY LIMIT 1;
You shouldn't tell that whenever we are using group by you shouldn't use where we can use "where" clause alang with group by.we can say that whenever we are using aggregate function to filter the aggregated value should use "having" clause before "group by" clause we can use where clause but after group by we can't use group by
You shouldn't tell that whenever we are using group by you shouldn't use where we can use "where" clause alang with group by.we can say that whenever we are using aggregate function to filter the aggregated value should use "having" clause before "group by" clause we can use where clause but after group by we can't use group by
2:58 *just a simple correction*
We can't use WHERE clause with AGGREGATE Function that's why we are using HAVING clause.
Good Bro...I was breaking my head with different ways of coding, you have given me a jump start. I appreciate it.....Keep GOING!!!!!!!
awesome explaination
Your concept is really good
2:54 Whenever we have aggregate function we cannot have WHERE clause on that aggregate function so we used HAVING clause.
--.Finding duplicates records
select emp_name, count(*)
from employee
group by emp_name
HAVING count(*) > 1
-- Scenario: If the Table contains multiple columns where only emp_name is duplicate, but rest of the values are unique.
--Using Windows function
select * from (
select emp_name,
ROW_NUMBER() OVER(PARTITION by emp_name) rn
from Employee)
where rn > 1
Thank you.
I would like to know the query for below question pls:
Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
SELECT CITY,LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY),CITY
LIMIT 1;
SELECT CITY,LENGTH(CITY)
FROM STATION
ORDER BY LENGTH(CITY) DESC,CITY
LIMIT 1;
Nice. Please upload on faq SQL queries
select ename from (
select ename, count(ename) as cnt
from table
group by 1) t
where cnt > 1
It's good . solve as many questions as possible
Thanks for the support .Plzz do like share and subscribe
It is not mandatory to use Having clause with group by
Yes! He meant we can't use WHERE clause with AGGREGATE functions.
Superb...
👍
Best Thanks
done
how to display the values in the decreasing order of frequencies?
just add "desc" in the last line that is: order by count(*) desc
Can anyone share the all sql queries in one file ?
You shouldn't tell that whenever we are using group by you shouldn't use where we can use "where" clause alang with group by.we can say that whenever we are using aggregate function to filter the aggregated value should use "having" clause
before "group by" clause we can use where clause but after group by we can't use group by
Class = clause :D
You shouldn't tell that whenever we are using group by you shouldn't use where we can use "where" clause alang with group by.we can say that whenever we are using aggregate function to filter the aggregated value should use "having" clause
before "group by" clause we can use where clause but after group by we can't use group by
Thank you for the information