All Forums General
g.lakshmipriya 4 posts Joined 05/15
21 May 2015
Teradata SQL

HI,
I have a column with both data and a null value for a unique PID .
 
eg:
PID   VALUE
1       A
1       Null
2       Null
3       Y
I want to populate the data into my report as
PID VALUE
1 A
2 Null
3 Y
 
Can anyone please help me to get the SQL for eliminating the null value  as specified.
 
Thanks in advance.
 
 

dnoeth 4628 posts Joined 11/04
21 May 2015
SELECT PID, MAX(VALUE)
FROM tab
GROUP BY 1;

 

Dieter

g.lakshmipriya 4 posts Joined 05/15
21 May 2015

Thanks Dnoeth..Can you suggest ,if its possible to use CASE statement to retrive the the same output.

dnoeth 4628 posts Joined 11/04
21 May 2015

No CASE because you need to work on multiple rows.
If you need additional columns you can utilize ROW_NUMBER:

SELECT *
FROM tab
QUALIFY
   ROW_NUMBER() 
   OVER (PARTITION BY PID ORDER BY VALUE DESC) = 1;

 

Dieter

You must sign in to leave a comment.