Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using MIN() -- unable to figure how and where to structure a subquery properly

Posted on 2011-09-09
9
Medium Priority
?
302 Views
Last Modified: 2012-05-12
Hello,

A common issue I'm having is how to use the MIN() function properly in a subquery so that I only return ONE record per row/ID.

I'm trying to find the earliest claim date that a member may have been diagnosed with COPD or emphysema during a specified time period and currently have:

SELECT DISTINCT t.n, c.CLAIM_NUM, c.ICD1, MIN(c.DOS)as MinDxDate, c.ICD2, c.ICD3, c.ICD4
-- n is a unique member id
INTO #temp_pcp_claims
FROM #temp_pcp t --temp table that has member and their respective PCPs in it
inner join dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID
WHERE                                             (c.ICD1 IN('491','492', '496')
      or c.ICD2 IN('491','492', '496')
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
GROUP BY t.n, DOS, CLAIM_NUM, c.ICD1, c.ICD2, c.ICD3, c.ICD4
ORDER BY t.N

It seem there has to be a subquery along the lines of (SELECT MIN(c.DOS) FROM claims); however, I cannot seem to fit it around the WHERE clause without causing errors and/or logic issues.

Any help much appreciated!!!      
0
Comment
Question by:karen1974
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36510070
please check this article I wrote, it should help:
http://www.experts-exchange.com/A_3203.html
note: it's not only for MySQL, so whatch out what applies to MySQL
0
 
LVL 15

Expert Comment

by:tim_cs
ID: 36510081
Remove DOS from your Group by.
0
 

Author Comment

by:karen1974
ID: 36510196
@ angelll -- this is a great article, I've actually read and referenced it before

@ time_cs -- removing the DOS from the GROUP BY decreases the number of records but it is still showing multiple DOS
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 15

Expert Comment

by:tim_cs
ID: 36510252
Try this out.  
;WITH CTE AS (
SELECT t.n, c.CLAIM_NUM, c.ICD1, c.DOS as MinDxDate, c.ICD2, c.ICD3, c.ICD4, ROW_NUMBER() OVER (PARTITION BY t.n ORDER BY C.DOS) RN
FROM #temp_pcp t 
inner join dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID 
WHERE                                             (c.ICD1 IN('491','492', '496') 
      or c.ICD2 IN('491','492', '496') 
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
ORDER BY t.N
)

SELECT n, Claim_Num, ICD1, MinDxDate, ICD2, ICD3, ICD4
INTO #temp_pcp_claims
FROM CTE
WHERE RN = 1

Open in new window

0
 

Author Comment

by:karen1974
ID: 36510369
Tim, I'm getting errors...
If I run the whole code, I get:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
0
 
LVL 15

Assisted Solution

by:tim_cs
tim_cs earned 500 total points
ID: 36510385
Oops, removed the group by but forgot the order by.  Remove that from inside the CTE.
0
 

Author Comment

by:karen1974
ID: 36510480
Tim, thanks -- I comment this out (below):

;WITH CTE AS (
SELECT t.n, c.CLAIM_NUM, c.ICD1, c.DOS as MinDxDate, c.ICD2, c.ICD3, c.ICD4,
      ROW_NUMBER() OVER (PARTITION BY t.n ORDER BY C.DOS) RN
FROM #temp_pcp t
inner join [nwhconn-beta].datawarehouse.dbo.CLAIMS c ON t.PCP_ID = c.PCP_ID
WHERE  (c.ICD1 IN('491','492', '496')
      or c.ICD2 IN('491','492', '496')
      or c.ICD3 IN('491','492', '496')
      or c.ICD4 IN('491','492', '496') )
AND DOS BETWEEN '2009/07/01' AND '2010/06/30'
--ORDER BY t.N
)

but I'm getting error about Incorrect syntax near ')'. It seems like all the parentheses are matched; sorry if I am overlooking something obvious!
0
 

Accepted Solution

by:
karen1974 earned 0 total points
ID: 36510595
My big mistake -- I hadn't run the entire program -- it works beautifully!!

Thank you very much for your assistance; much appreciation!!
0
 

Author Closing Comment

by:karen1974
ID: 36597717
Works in my code; thank you very much
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

660 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