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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Trying to fix a query with OVER and PARTITION BY
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bazinga
Starting Member

19 Posts

Posted - 05/23/2013 :  10:45:08  Show Profile  Reply with Quote
Background
The database contains a table storing gift/donation data. I have a SQL view that figures out the two most recent gifts per Household_ID based on the gifteffdate field. The view assigns each row per Household_ID a Row_Number, and then only shows the first 2 rows per Household_ID in the results. Here is the SQL for this view:

SQL
********************************************************
SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Household_ID
ORDER BY gifteffdat DESC) AS 'RowNumber', *
FROM milladmin.gifts_g_y_b_c_with_household_id) dt
WHERE (RowNumber <= 2)
AND (gifttype IN ('g', 'y', 'b', 'c')
AND giftjntkey <> '1'
)
********************************************************

I've noticed that the giftjntkey exclusion is processing AFTER the Row Numbers have been assigned, and the undesired result is if a Row Number with a value of 1 or 2 contains the gifrjntkey = 1, then that row is eliminated from the results. What I want to happen is to have that exclusion take place before the ROW Number is assigned, so that I still end up with two row numbers per Household_ID.

Make sense?

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  11:02:27  Show Profile  Visit russell's Homepage  Reply with Quote
WITH gifts AS
(
	SELECT	ROW_NUMBER() OVER (PARTITION BY Household_ID ORDER BY gifteffdat DESC) AS RowNumber, *
	FROM 	milladmin.gifts_g_y_b_c_with_household_id) dt
	WHERE 	gifttype IN ('g', 'y', 'b', 'c')
	AND 	giftjntkey <> '1'
)
SELECT * FROM gifts WHERE RowNumber <= 2

Edited by - russell on 05/23/2013 11:02:54
Go to Top of Page

Bazinga
Starting Member

19 Posts

Posted - 05/23/2013 :  11:44:10  Show Profile  Reply with Quote
That works. Thanks!
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 05/23/2013 :  12:34:13  Show Profile  Visit russell's Homepage  Reply with Quote
Welcome
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000