SQL Server Forums
Profile | Register | 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
 New Topic  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  
 New 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.08 seconds. Powered By: Snitz Forums 2000