Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

group by, distinct not working as expected Expand / Collapse
Author
Message
Posted Thursday, January 2, 2014 4:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:01 AM
Points: 92, Visits: 148
Hi,

I have this sql....

SELECT
dbPatID, dbAddDate, dbStaffLastName, RefTypeWord
FROM
EPSReferralKPIs
WHERE
(dbAddDate >= '2013-01-01' OR '2013-01-01' = '')
AND (dbAddDate <= '2013-12-31' OR '2013-12-31' = '')
AND (dbStaffLastName IN ('Swanepoel','Patient','Pelletti','Ray','Qureshi','Grobler','Hedborg','De Kock','Lima','Check In','Hodgson')
AND (RefTypeWord IN ('PATIENT','OTHER','DOCTOR','','SIBLING')
ORDER BY
dbAddDate

There may be more than one RefTypeWord for a dbPatId however I only want the result set to bring back unique dbPatID's and not more than one row containing a different RefTypeWord per row.
Is that possible?
thanks,
Post #1527031
Posted Thursday, January 2, 2014 4:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
What about the other columns? Can you show a few rows of what you have and what you would like to see? Remember - we can't see what you can, we're relying upon your description.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527036
Posted Thursday, January 2, 2014 4:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:01 AM
Points: 92, Visits: 148
The current sql will bring back

90003259 2013-05-28 16:05:13.000 Grobler DOCTOR
90003259 2013-05-28 16:05:13.000 Grobler OTHER

Where as I want it to bring back just one row for each dbPatID, I'm not bothered which RefTypeWord it is

thanks
Post #1527038
Posted Thursday, January 2, 2014 4:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
Try this as a test:

SELECT
dbPatID,
dbAddDate,
dbStaffLastName,
RefTypeWord = MAX(RefTypeWord)

FROM EPSReferralKPIs

WHERE
dbAddDate >= '2013-01-01' OR '2013-01-01' = ''
AND dbAddDate <= '2013-12-31' OR '2013-12-31' = ''
AND dbStaffLastName IN ('Swanepoel','Patient','Pelletti','Ray','Qureshi','Grobler','Hedborg','De Kock','Lima','Check In','Hodgson')
AND RefTypeWord IN ('PATIENT','OTHER','DOCTOR','','SIBLING')

GROUP BY dbPatID, dbAddDate, dbStaffLastName

ORDER BY dbAddDate



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527040
Posted Thursday, January 2, 2014 5:26 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 6:01 AM
Points: 92, Visits: 148
Thanks, I've actually worked out we don't need to select the RefTypeWord, so removing this from the select and making dbPatID distinct bought back the correct amount of rows.

I compared this with your sql and it brings the same result set back which is good.

thanks for your help.
Post #1527052
Posted Thursday, January 2, 2014 5:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 7,120, Visits: 13,491
Just a thought...what's this meant to do?

(dbAddDate >= '2013-01-01' OR '2013-01-01' = '') 
AND (dbAddDate <= '2013-12-31' OR '2013-12-31' = '')



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1527064
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse