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.
| Author |
Topic |
|
Bazinga
Starting Member
19 Posts |
 Posted - 2013-05-23 : 10:45:08
|
| BackgroundThe 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_IDORDER BY gifteffdat DESC) AS 'RowNumber', *FROM milladmin.gifts_g_y_b_c_with_household_id) dtWHERE (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
5072 Posts |
Posted - 2013-05-23 : 11:02:27
|
| [code]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[/code] |
 |
|
|
Bazinga
Starting Member
19 Posts |
Posted - 2013-05-23 : 11:44:10
|
| That works. Thanks! |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-05-23 : 12:34:13
|
Welcome  |
|
|
|
|
|
|
|
|
Trying to fix a query with OVER and PARTITION BY