MIN and MAX values with a subquery.

Question

Post reply

MIN and MAX values with a subquery.

Johnny H

Right there with Babe

Points: 799
More actions
July 11, 2013 at 2:59 pm

#277269

Hi Everyone
I have the following query in one of my databases. It returns 20 rows.
SELECT C_ID, H_Type, Name, A_Name, D_Date as 'First Date', D_Status, D_TYPE
FROM DH_info
WHERE (D_Date IN
(SELECT MIN(D_Date) AS First_date
FROM DH_info
GROUP BY C_ID, D_TYPE))
I now have to add MAX(D_DATE) to the query . The query should return
C_ID, H_Type, Name, A_Name, MIN(D_Date) as 'First Date', MAX(D_Date) as 'Last Date', D_Status, D_TYPE
Can any one help me as I have been working on this for hours without luck.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259015 More actions · Answer 1

SELECT C_ID, H_Type, Name, A_Name, Min(D_Date) as 'First Date', Max(D_Date) as 'Last Date', D_Status, D_TYPE

FROM DH_info

WHERE (D_Date IN

(SELECT MIN(D_Date) AS First_date

FROM DH_info

GROUP BY C_ID, D_TYPE))

GROUP BY C_ID, H_Type, Name, A_Name, D_Date as 'First Date', D_Status, D_TYPE

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

Something like this?

;WITH DH_info (C_ID,H_Type,Name,A_Name,D_Date,D_Status,D_TYPE)

AS

(

SELECT 1,'22','George','Washington','2012-01-09','U','23' UNION ALL

SELECT 2,'33','John','Adams','2013-01-09','I','29' UNION ALL

SELECT 1,'22','George','Washington','2013-04-09','U','23' UNION ALL

SELECT 3,'55','James','Monroe','2012-01-29','I','23' UNION ALL

SELECT 3,'33','James','Monroe','2011-10-15','I','29' UNION ALL

SELECT 1,'77','George','Washington','2013-07-09','U','23' UNION ALL

SELECT 2,'88','John','Adams','2013-07-10','X','23'

)

SELECT

r.C_ID

,r.D_TYPE

,r.H_Type

,r.Name

,r.A_Name

,r.FirstDate

,r.LastDate

,r.D_Status

FROM

(

SELECT

ROW_NUMBER() OVER (PARTITION BY d1.C_ID, d1.D_TYPE ORDER BY d1.C_ID) AS rowNum

,d1.C_ID

,d1.D_TYPE

,d1.H_Type

,d1.Name

,d1.A_Name

,MIN(d1.D_Date) OVER (PARTITION BY d1.C_ID, d1.D_TYPE) AS FirstDate

,MAX(d1.D_Date) OVER (PARTITION BY d1.C_ID, d1.D_TYPE) AS LastDate

,d1.D_Status

FROM

DH_info AS d1

) r

WHERE

rowNum = 1

Johnny H Right there with Babe Points: 799 More actions · Answer 3

Hi

This query returns the same values for both the min and max date.

Johnny H Right there with Babe Points: 799 More actions · Answer 4

Thank You Steven, this worked great. I never thought of using PARTITION method. 🙂