possible partition query - 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 2008 Forums

Transact-SQL (2008)

possible partition query

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2010-06-19 : 00:05:28

Not sure if this is a group by or a partition query.

I have a table:

id pos effectivedate
1 23 1/1/2010
2 23 3/3/2010
3 24 1/1/2010
4 25 4/7/2010
5 25 5/5/2010
6 25 7/7/2010

I have to create a query where the EffectiveDate <= 6/6/2010
grouped by position and using the position with the most recent date.

So the results would be

id pos effectivedate
2 23 3/3/2010
3 24 1/1/2010
5 25 5/5/2010

I can get a result not filtering the effective date and not including the ID, i.e. that would be grouping the pos by the most recent effective date, but I can't seem to figure out how to only select the dates <= 5/5/2010 and also to keep the id in the query since I need it for later joins. I tried using having max(effectivedate) <= ... but that did not group the pos correctly.

Would appreciate some help on this.

Thanks so much.

grasshopper
Starting Member

1 Post

Posted - 2010-06-19 : 06:10:58

You can use a common table expression (available in SQL 2005 and SQL 2008) as follows:

------------------------------------------------------
WITH MaxTable ( pos, effectivedate)
AS
( select pos, max(effectivedate) AS MAXDate
from t1
WHERE effectivedate <= CONVERT(VARCHAR,'6/6/2010',1)
group by pos
)
SELECT t.ID, t.POS, CONVERT(VARCHAR,t.EFFECTIVEDATE,101) AS EFFECTIVEDATE
FROM t1 t INNER JOIN MaxTable m ON t.POS = m.POS
AND t.effectivedate = m.effectivedate
ORDER BY t.ID
------------------------------------------------------

The result will be:

ID POS EFFECTIVEDATE
----------- ----------- ------------------------------
2 23 03/03/2010
3 24 01/01/2010
5 25 05/05/2010

(3 row(s) affected)

smh
Yak Posting Veteran

94 Posts

Posted - 2010-06-19 : 12:18:10

Thank you. This works like a charm. I have never seen the Common Table Expression. This is quite useful.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources