can i use over clause after having on sql server 2012 ?

Question

can i use over clause after having on sql server 2012 ?

ahmed salah 3,216

can i use over clause after having on sql server 2012 ?
I have technical question
can i use over clause over count on having clause
as example

select partid,product
from product
having count(partid) over

are this valid please answer me

what i know is over can use before from
but are this possible after having
having over partition or
having over order by

EchoLiu-MSFT 14,621 Reputation points

2021-03-17T07:47:02.56+00:00

Do you have any update?Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Accepted answer

1 additional answer

Your answer

EchoLiu-MSFT 14,621 Reputation points

2021-03-17T07:47:02.56+00:00

Do you have any update?Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Echo

Answer 1

EchoLiu-MSFT 14,621

Windowed functions can only appear in the SELECT or ORDER BY clauses.The example mentioned in your question is invalid and will return an error：

    select partid,product
    from product
    having count(partid) over

Having is to specify filter conditions for the group or or an aggregate returned in the previous step.

Echo

Answer 2

Guoxiong 8,206

You cannot use the OVER clause in the HAVING clause. Give some examples and tell what you want to do.

ahmed salah 3,216 Reputation points

2021-03-15T21:13:14.51+00:00

can i use over statment
after from table
without having
Guoxiong 8,206 Reputation points

2021-03-15T21:25:02.327+00:00

You can use it in the subquery after the FROM clause, but you cannot use it directly in the FROM clause.
ahmed salah 3,216 Reputation points

2021-03-15T21:26:20.303+00:00

can you show me sample for that please

Guoxiong 8,206

Here is an example:

DECLARE @T TABLE (  
	LastName varchar(50),  
	FirstName varchar(50)  
);  
INSERT INTO @T VALUES  
('Last1', 'First1'), ('Last2', 'First2'), ('Last1', 'First1'), ('Last3', 'First3');  
  
-- List names without duplicates  
SELECT t.LastName, t.FirstName  
FROM (  
	-- Subquery  
	SELECT LastName, FirstName, ROW_NUMBER() OVER(PARTITION BY LastName, FirstName ORDER BY LastName, FirstName) AS RankedNumber  
	FROM @T  
) AS t  
WHERE t.RankedNumber = 1;

Share via

can i use over clause after having on sql server 2012 ?

1 additional answer

Your answer