Difference between Qualify and Having

teradata
pri picture pri · Oct 27, 2015 · Viewed 11.2k times · Source

Can someone please explain me, what is the difference between qualify...over...partition by and group by...having in Teradata?
I would also like to know if there are any differences in their performances.

Answer

dnoeth picture dnoeth · Oct 27, 2015

QUALIFY is a proprietary extension to filter the result of a Windowed Aggregate Function.

A query is logically processed in a specific order:

  1. FROM: create the basic result set
  2. WHERE: remove rows from the previous result set
  3. GROUP BY: apply aggregate functions on the previous result set
  4. HAVING: remove rows from the previous result set
  5. OVER: apply windowed aggregate functions on the previous result set
  6. QUALIFY: remove rows from the previous result set

Related questions

SQL SELECT multi-columns INTO multi-variable

I'm converting SQL from Teradata to SQL Server in Teradata, they have the format SELECT col1, col2 FROM table1 INTO @variable1, @variable2 In SQL Server, I found SET @variable1 = ( SELECT col1 FROM table1 ); That only allows a single column/variable …

Read More
What is ROWS UNBOUNDED PRECEDING used for in Teradata?

I am just starting on Teradata and I have come across an Ordered Analytical Function called "Rows unbounded preceding" in Teradata. I tried several sites to learn about the function but all of them uses a complicated example explaining the …

Read More
Find which rows have different values for a given column in Teradata SQL

I am trying to compare two addresses from the same ID to see whether they match. For example: Id Adress Code Address 1 1 123 Main 1 2 123 Main 2 1 456 Wall 2 2 456 Wall 3 1 789 Right 3 2 100 Left I'm just trying to figure out whether the address for each ID …

Read More