?
Solved

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

Posted on 2007-08-08
1
Medium Priority
?
196 Views
Last Modified: 2013-11-30
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?
0
Comment
Question by:JulieGrace
1 Comment
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 19656123
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question