Using Aggregate Function

Question

4.00/5 (1 vote)

See more:

SQL-Server-2008

Hi all,

I have a table called Groups with columns GroupID and release date.

I need to write a query which searches the group id based on oldest release date and the result should show latest release date for the group. For example,

GroupID	ReleaseDate
101	7/30/2012
101	8/1/2012
102	7/31/2012
102	8/2/2012
102	8/5/2012
103	8/1/2012
103	8/6/2012

Query -

SQL

Select GroupID, ReleaseDate 
            	From Groups 
	Where ReleaseDate Between '7/28/2012' and '7/31'2012'

Result should be as follows -

GroupID ReleaseDate
101	8/1/2012
102	8/5/2012

Please note that Serach criteria is based on Oldest date. i.e. it should check if MIN(ReleaseDate) between '7/28/2012' and '7/31'2012' for each Group. Here only 101 and 102 satisfies the condition but for 103 Min(ReleaseHoldDate) is '8/1/2012'

But in the result it should show MAX(Release Date) for the group

Query -

SQL

Select GroupID, ReleaseDate 
            	From Groups 
	Where ReleaseDate Between '8/1/2012' and '8/5/2012'

Result should be as follows -

GroupID ReleaseDate
103	8/6/2012

I have tried like below for the 1st query, but did not work.

SQL

SELECT GroupID, MAX(ReleaseHoldDate)
FROM Groups
GROUP BY GroupID,ReleaseHoldDate 		 			 
HAVING  MIN(ReleaseHoldDate) BETWEEN '7/28/2012' AND '7/31/2012'

This did not work. Please help.

Thanks in advance,
SR

Posted 2-Aug-12 0:27am

SruthiR

Updated 2-Aug-12 1:07am

Prasad_Kulkarni

v2

Add a Solution

4 solutions

Solution 2

you need to use subqueries

SQL

select groupID,releasedate from groups
where groupid in (select groupid from groups
                  where releasedate = max(releasedate)
                  group by groupid) 
      or
      groupid in (select groupid from groups
                  where releasedate = min(releasedate) 
                  group by groupid)

Posted 2-Aug-12 0:52am

Philip Stuyck

Updated 2-Aug-12 0:56am

v2

Comments

SruthiR 2-Aug-12 7:23am

I need to get max(releasedate) in the result

Philip Stuyck 2-Aug-12 10:18am

I misunderstood what you wanted. You want the mas releasedate per group

Solution 3

A subquery will do the trick

SQL

Select GroupID, max(ReleaseDate)
From Groups
where groupid in
(
select GroupID from Groups Where ReleaseDate Between '7/28/2012' and '7/31/2012'
)
group by GroupID

Posted 2-Aug-12 0:55am

Chabi Blazquez

Comments

SruthiR 2-Aug-12 7:24am

I need to check with min(releasedate) in the search

Solution 4

Try this

CREATE TABLE #Temp
(
	GroupID INT,
	ReleaseDate DATETIME
)

INSERT INTO #Temp
SELECT 101, '7/30/2012' UNION
SELECT 101, '8/1/2012' UNION
SELECT 102, '7/31/2012' UNION
SELECT 102, '8/2/2012' UNION
SELECT 102, '8/5/2012' UNION
SELECT 103, '8/1/2012' UNION
SELECT 103, '8/6/2012'



SELECT  T1.GroupID, CONVERT(VARCHAR(10),MAX(ReleaseDate),101) AS ReleaseDate FROM #Temp T1
INNER JOIN
(
	SELECT GroupID, MIN(ReleaseDate) AS MinDate FROM #Temp
	GROUP BY GroupID
	HAVING MIN(ReleaseDate) Between'8/1/2012' and '8/5/2012'
) T2 ON T1.GroupID = T2.GroupID
GROUP BY T1.GroupID

Posted 2-Aug-12 1:03am

__TR__

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Accepted Answer · 2012-08-02T00:51:00

Solution 1

SQL

select groupId,MAX(releasedate) from temp where groupId in
    (select groupId from temp t where releasedate in
        (Select min(releasedate) from temp  where releasedate between  '7/28/2012' and '7/31/2012'
        and groupId=t.groupId)
 group by  groupId )
 group by groupId

Posted 2-Aug-12 0:51am

Santhosh Kumar Jayaraman

Comments

SruthiR 2-Aug-12 7:21am

It worked. Thanks a lot!!!

Santhosh Kumar Jayaraman 2-Aug-12 7:50am

Welcome