TRYING TO GET A DISTINCT RECORD SET BASED ON TWO COLUMNS; BUT NEED TO INCLUDE OTHER COLUMNS WHICH MAKE THE SELECT NON-DISTINCT

WHEN I USE THE FOLLOWING QUERY:

select distinct
CONTRACT_NUM
,REL_CTGY_CD
into CONT_FB_REL
from FLEXBLUE_FORM_SMART_MBRS
where rel_ctgy_cd = '1'
or rel_ctgy_cd ='2'
HAVING COUNT(*) = 1

(28427 row(s) affected)

I GET THE EXACT NUMBER OF ROWS I AM LOOKING FOR.

THE PROBLEM IS THAT WHEN I ADD THE REST OF THE COLUMNS REQUIRED FOR A TABLE I AM BUILDING,  LIKE  GROUP_ID, AND SO FOR,  I LOOSE THE NUMBER OF RECORDS I AM LOOKING FOR

(139486 row(s) affected)

ANY IDEAS?
JulieGraceAsked:
Who is Participating?
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
In that case you can put some sort of aggregate functions on that columns

select distinct
CONTRACT_NUM
,REL_CTGY_CD, MAX(Group_ID) Group_ID
into CONT_FB_REL
from FLEXBLUE_FORM_SMART_MBRS
where rel_ctgy_cd = '1'
or rel_ctgy_cd ='2'
HAVING COUNT(*) = 1

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.