GROUP BY Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

GROUP BY Question

Author

Topic

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-19 : 04:39:39

Hi Team,

I have three tables as follows. For a funding, I would like to select the latest Letter created date and the ‘earliest worklist created since letter created’ date for a funding.

FundingId Leter
1 1/1/2009
1 5/5/2009
1 8/8/2009
2 3/3/2009

FundingId WorkList
1 5/5/2009
1 9/9/2009
1 10/10/2009
2 2/2/2009

Expected Result
FundingId Leter WorkList
1 8/8/2009 9/9/2009

I wrote a query as follows. It has a bug. It will omit those FundingId for which the minimum WorkList date is less than latest Letter date (even though it has another worklist with greater than letter created date).

CREATE TABLE #Funding(
[Funding_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_No] [int] NOT NULL,
CONSTRAINT [PK_Center_Center_ID] PRIMARY KEY NONCLUSTERED ([Funding_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #Letter(
[Letter_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_ID] [int] NOT NULL,
[CreatedDt] [SMALLDATETIME],
CONSTRAINT [PK_Letter_Letter_ID] PRIMARY KEY NONCLUSTERED ([Letter_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #WorkList(
[WorkList_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_ID] [int] NOT NULL,
[CreatedDt] [SMALLDATETIME],
CONSTRAINT [PK_WorkList_WorkList_ID] PRIMARY KEY NONCLUSTERED ([WorkList_ID] ASC)
) ON [PRIMARY]

SELECT F.Funding_ID,
Funding_No,
MAX (L.CreatedDt),
MIN(W.CreatedDt)
FROM #Funding F
INNER JOIN #Letter L ON L.Funding_ID = F.Funding_ID
LEFT OUTER JOIN #WorkList W ON W.Funding_ID = F.Funding_ID
GROUP BY F.Funding_ID,Funding_No
HAVING MIN(W.CreatedDt) > MAX (L.CreatedDt)

How can I write a correct query without using subquery?

Please help

Thanks & Regards
Lijo Cheeran Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 12:54:28

[code]
SELECT l.FundingId,l.MaxLetterDate,f.MinWorkListDate
FROM (SELECT FundingId,MAX(Letter) AS MaxLetterDate
FROM #Letter
GROUP BY FundingId)l
CROSS APPLY (SELECT TOP 1 WorkList AS MinWorkListDate
FROM #Letter
WHERE FundingId=l.FundingId
AND WorkList > l.MaxLetterDate
ORDER BY WorkList ASC)f
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-19 : 15:46:51

quote:
Originally posted by visakh16

SELECT l.FundingId,l.MaxLetterDate,f.MinWorkListDate
FROM (SELECT FundingId,MAX(Letter) AS MaxLetterDate
FROM #Letter
GROUP BY FundingId)l
CROSS APPLY (SELECT TOP 1 WorkList AS MinWorkListDate
FROM #Letter
WHERE FundingId=l.FundingId
AND WorkList > l.MaxLetterDate
ORDER BY WorkList ASC)f

I always avoid using TOP 1 and ORDER BY together like this to find an end point. In this case, SELECT MIN(WorkList) AS MinWorkListDate seems more logical.

I have seen cases where TOP 1/ORDER BY has caused serious performance decreases on large quantities of data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 01:39:13

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16

SELECT l.FundingId,l.MaxLetterDate,f.MinWorkListDate
FROM (SELECT FundingId,MAX(Letter) AS MaxLetterDate
FROM #Letter
GROUP BY FundingId)l
CROSS APPLY (SELECT TOP 1 WorkList AS MinWorkListDate
FROM #Letter
WHERE FundingId=l.FundingId
AND WorkList > l.MaxLetterDate
ORDER BY WorkList ASC)f

I always avoid using TOP 1 and ORDER BY together like this to find an end point. In this case, SELECT MIN(WorkList) AS MinWorkListDate seems more logical.

I have seen cases where TOP 1/ORDER BY has caused serious performance decreases on large quantities of data.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

There are 10 types of people in the world, those that understand binary, and those that don't.

That really depends on lot of factors like presence of proper index, amount of actual data etc

See below for comparison

http://www.sqlservercentral.com/articles/T-SQL/69481/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-20 : 12:23:30

quote:
Originally posted by visakh16
That really depends on lot of factors like presence of proper index, amount of actual data etc

You'll be doing well to find a situation where it takes longer to find the minimum or maximum in a series than it does to sort the entire series and then select a the first or last row.

I have seen many situations when replacing a "SELECT TOP 1/ORDER BY" statement with a "SELECT MIN/MAX" has dramatically increased performance. I've never seen it decrease performance. For that reason alone, I avoid "SELECT TOP 1/ORDER BY" where ever possible.

There are 10 types of people in the world, those that understand binary, and those that don't.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-20 : 12:25:44

quote:
Originally posted by DBA in the making

quote:
Originally posted by visakh16
That really depends on lot of factors like presence of proper index, amount of actual data etc

You'll be doing well to find a situation where it takes longer to find the minimum or maximum in a series than it does to sort the entire series and then select a the first or last row.

I have seen many situations when replacing a "SELECT TOP 1/ORDER BY" statement with a "SELECT MIN/MAX" has dramatically increased performance. I've never seen it decrease performance. For that reason alone, I avoid "SELECT TOP 1/ORDER BY" where ever possible.

There are 10 types of people in the world, those that understand binary, and those that don't.

Ok
but did you see the test results given in link?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-20 : 12:40:10

quote:
Originally posted by visakh16
Ok
but did you see the test results given in link?

No, I don't have a login for that site, and make a habit of not registering at forums that require it for browsing.

There are 10 types of people in the world, those that understand binary, and those that don't.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-27 : 03:53:32

Thanks.

Can the following give a better performance?

SELECT F.Funding_ID,StartDate,MIN(CreatedDt) AS EndDate
FROM #Funding F
INNER JOIN
(
SELECT F2.Funding_Id,MAX(CreatedDt) StartDate
FROM #Funding F2
INNER JOIN #Letter L ON F2.Funding_ID = L.Funding_ID
GROUP BY F2.Funding_Id
) Start ON Start.Funding_Id = F.Funding_Id
LEFT OUTER JOIN #WorkList W
ON Start.Funding_ID = w.Funding_ID
WHERE W.CreatedDt >= Start.StartDate
GROUP BY F.Funding_ID,StartDate

Thanks
Lijo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 04:33:05

quote:
Originally posted by Lijo Cheeran Joseph

Thanks.

Can the following give a better performance?

SELECT F.Funding_ID,StartDate,MIN(CreatedDt) AS EndDate
FROM #Funding F
INNER JOIN
(
SELECT F2.Funding_Id,MAX(CreatedDt) StartDate
FROM #Funding F2
INNER JOIN #Letter L ON F2.Funding_ID = L.Funding_ID
GROUP BY F2.Funding_Id
) Start ON Start.Funding_Id = F.Funding_Id
LEFT OUTER JOIN #WorkList W
ON Start.Funding_ID = w.Funding_ID
WHERE W.CreatedDt >= Start.StartDate
GROUP BY F.Funding_ID,StartDate

Thanks
Lijo

what does #WorkList contain? also you're using W.CreatedDt >= Start.StartDate in WHERE in which it reduces to INNER JOIN effect and hence you can use INNER rather than LEFT join. if your requirement is to bring all records from initial table regardless of match in #WorkList then add the above condition in ON rather than WHERE

ie

LEFT OUTER JOIN #WorkList W
ON Start.Funding_ID = w.Funding_ID
AND W.CreatedDt >= Start.StartDate

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

xpandre
Posting Yak Master

212 Posts

Posted - 2010-03-27 : 04:51:57

out of context...but nice link Visakh..thanks for sharing..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 04:54:53

quote:
Originally posted by xpandre

out of context...but nice link Visakh..thanks for sharing..

you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-27 : 08:31:33

I doubt whether there is a bug in the CROSS APPLY Approach (given below). It does not list the Funindg_ID 2. Could you please explain why?

Note: It is the first time I am using CROSS APPLY. Please be patient even if this question is wrong.

The following explains what is contained in the tables.
CREATE TABLE #Funding(
[Funding_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_No] [int] NOT NULL,
CONSTRAINT [PK_Center_Center_ID] PRIMARY KEY NONCLUSTERED ([Funding_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #Letter(
[Letter_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_ID] [int] NOT NULL,
[CreatedDt] [SMALLDATETIME],
CONSTRAINT [PK_Letter_Letter_ID] PRIMARY KEY NONCLUSTERED ([Letter_ID] ASC)
) ON [PRIMARY]

CREATE TABLE #WorkList(
[WorkList_ID] [int] IDENTITY(1,1) NOT NULL,
[Funding_ID] [int] NOT NULL,
[CreatedDt] [SMALLDATETIME],
CONSTRAINT [PK_WorkList_WorkList_ID] PRIMARY KEY NONCLUSTERED ([WorkList_ID] ASC)
) ON [PRIMARY]

INSERT INTO #Funding (Funding_No) VALUES (1)
INSERT INTO #Funding (Funding_No) VALUES (2)

INSERT INTO #Letter (Funding_ID,CreatedDt) VALUES (1,'1/1/2009')
INSERT INTO #Letter (Funding_ID,CreatedDt) VALUES (1,'5/5/2009')
INSERT INTO #Letter (Funding_ID,CreatedDt) VALUES (1,'8/8/2009')
INSERT INTO #Letter (Funding_ID,CreatedDt) VALUES (2,'3/3/2009')

INSERT INTO #WorkList (Funding_ID,CreatedDt) VALUES (1, '5/5/2009')
INSERT INTO #WorkList (Funding_ID,CreatedDt) VALUES (1, '9/9/2009')
INSERT INTO #WorkList (Funding_ID,CreatedDt) VALUES (1, '10/10/2009')
INSERT INTO #WorkList (Funding_ID,CreatedDt) VALUES (2, '2/2/2009')

--Answer 1 INLINE VIEW
SELECT F.Funding_ID,StartDate,MIN(CreatedDt) AS EndDate
FROM #Funding F
INNER JOIN
(
SELECT F2.Funding_Id,MAX(CreatedDt) StartDate
FROM #Funding F2
INNER JOIN #Letter L ON F2.Funding_ID = L.Funding_ID
GROUP BY F2.Funding_Id
) Start ON Start.Funding_Id = F.Funding_Id
LEFT OUTER JOIN #WorkList W
ON Start.Funding_ID = w.Funding_ID
AND W.CreatedDt >= Start.StartDate
GROUP BY F.Funding_ID,StartDate

-- Approach 2 CROSS APPLY
SELECT Letter.Funding_ID,Letter.MaxLetterDate,WorkList.MinWorkListDate
FROM
( SELECT Funding_ID,MAX(CreatedDt) AS MaxLetterDate
FROM #Letter
GROUP BY Funding_ID
)Letter

CROSS APPLY

( SELECT TOP 1 CreatedDt AS MinWorkListDate
FROM #WorkList
WHERE Funding_ID = Letter.Funding_ID
AND CreatedDt > Letter.MaxLetterDate
ORDER BY CreatedDt ASC
)WorkList

Thanks
Lijo

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-27 : 08:40:32

quote:
Originally posted by Lijo Cheeran Joseph

I doubt whether there is a bug in the CROSS APPLY Approach (given below). It does not list the Funindg_ID 2. Could you please explain why?

Because there is no matching record coming from the inner query. Change the CROSS APPLY operator to OUTER APPLY, and see what happens. Also, have a read of the CROSS APPLY page in Books on line.

There are 10 types of people in the world, those that understand binary, and those that don't.

Lijo Cheeran Joseph
Posting Yak Master

123 Posts

Posted - 2010-03-27 : 08:48:13

Thanks. That is the reason

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-27 : 09:36:55

quote:
Originally posted by Lijo Cheeran Joseph

Thanks. That is the reason

its like INNER and LEFT JOIN. CROSS APPLY returns result only if it has match from correlated query/function whereas OUTER APPLY returns everything from left part regardless of a match

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources