COMPUTER SCIENCE AND ENGINEERING
SQL
|
Question
[CLICK ON ANY CHOICE TO KNOW THE RIGHT ANSWER]
|
|
|
Yes
|
|
|
No
|
|
|
Depends on the function
|
|
|
Depends on the number of impacted records
|
Detailed explanation-1: -The reason why window functions are not allowed in GROUP BY is the order of operations in SQL. The clauses of a SQL query are processed in a different order than they are written in the query.
Detailed explanation-2: -Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT .
Detailed explanation-3: -GROUP BY functionality only offers aggregate functions; whereas Window functions offer aggregate, ranking, and value functionalities.
Detailed explanation-4: -Window functions increase the efficiency and reduce the complexity of queries that analyze partitions (windows) of a data set by providing an alternative to more complex SQL concepts, e.g. derived queries. Common use cases include: Ranking results within a specific window (e.g. per-group ranking)
Detailed explanation-5: -A query with a having clause should also have a group by clause. If you omit group by, all the rows not excluded by the where clause return as a single group. Because no grouping is performed between the where and having clauses, they cannot act independently of each other.