Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-09
9
Medium Priority
?
304 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

927 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