All Forums Database
vbdcb 14 posts Joined 12/09
10 Dec 2014
Using DISTINCT with Ordered Analytic Functions and Dates

I have run into a perplexing little problem. I know how to work around it, but want to understand the reason it is occuring.

 

 

 

When I use the FIRST_VALUE function on a date as below, it returns two rows:

SELECT  
	l.Id	ID, 
	FIRST_VALUE(t.LASTMODIFIEDDATE) OVER(PARTITION BY l.ID ORDER BY t.LASTMODIFIEDDATE)	First_Contacted

 

I only want to return a single row, so I add DISTINCT to the code:

SELECT DISTINCT
	l.Id	ID, 
	FIRST_VALUE(t.LASTMODIFIEDDATE) OVER(PARTITION BY l.ID ORDER BY t.LASTMODIFIEDDATE)	First_Contacted

This returns the error: Failed [5407 : HY000] Invalid operation for DateTime or Interval. 

Why????

 

 

You must sign in to leave a comment.