SQL Help!!!

[tektipuser2008]

Using Teradata ordered analytical functions , is it possible to get the result set below. If yes, can someone please provide me with some hints or the SQL itself.
Any help with this is much appreciated.

Sample Data :

Col1 Col2

D1 K1
D2 K1
D3 K2
D3 K4
D4 K1
D5 K2

Result set :

Col1 Col2

D1 K1
D3 K2

Explanation : Since K1 is associated with D1, all other associations of K1 should be dropped, similarly since D3 is associated with K2 all other associations of D3 should be dropped.

 

[r937]

perhaps forum328 would be a better place to ask this question

if we gave you an ANSI SQL solution, are you sure it would work in teradata?



r937.com | rudy.ca

 

[tektipuser2008]

ANSI SQL solution would sure work.

 

[r937]

okay, fine, let's try the ANSI SQL route

Explanation : Since K1 is associated with D1, all other associations of K1 should be dropped, similarly since D3 is associated with K2 all other associations of D3 should be dropped.

are you sure this is the only information you can supply?


because the ANSI SQL solution goes like this --

SELECT col1
     , col2
  FROM sample
 WHERE col1 = 'D1' AND col2 = 'K1'
    OR col1 = 'D3' AND col2 = 'K2'

r937.com | rudy.ca

 

[PHV]

SELECT Col1, Min(Col2)
FROM yourTable
GROUP BY Col1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886