none
Problem getting single output row from many.

    Question

  • my query returns multiple rows. I am using Row_Number() to order them. I then select Row = 1, which should show me only the row that is first in the list. Instead, I'm getting 6 rows of output.

    My query is as follows:

    set nocount on
    declare @today datetime 
    set @today=utility.dbo.fn_stripdate(getdate())
    
    if datepart(dw,@today)=2
    	select @today=@today-3
    else
    	select @today=@today-1
    
    declare @firstofmonth datetime=utility.dbo.fn_monthfirst(@today)
    declare @lastmonthtoday datetime=utility.dbo.fn_lmsameworkday(@today)
    declare @firstoflastmonth datetime=dateadd(m,-1,@firstofmonth)
    
    /* if @today is a holiday, go back to previous day */
    declare @t datetime,@w int,@tgt int
    select @t=@firstofmonth,@w=0,@tgt=utility.dbo.fn_workingdays(@firstofmonth,@today)
    if utility.dbo.fn_workingdays(@today,@today)=0
    begin
    	while utility.dbo.fn_workingdays(@firstofmonth,@t)<@tgt
    	begin
    		select @t+=1
    	end
    	select @today=@t
    end
    --select @today='20121231'
    declare @reportcode varchar(15)
    select @reportcode= case @regid when 3 then 'SCA' when 98 then 'NCA' else null end
    declare @branches table (branch varchar(15), regid int)
    select @regid=case when @regid=98 then 3 else @regid end
    insert into @branches
    select branch,regid from ccwfgnationaltitle.dbo.BranchDef where regid=@regid
    			and isnull(reportcode,'')=coalesce(@reportcode,isnull(reportcode,''))
    
    CREATE TABLE #tempDistinctCount (GFNo varchar(20) , Fee_Date datetime)
    
    insert into #TempDistinctCount
    
    select distinct(et.gfno)
    		,case
    			when oc.checkdate is not null
    			then min(oc.CheckDate) 
    			else	min(im.invoicedate)
    			end as FeeDate
    		from dbo.orders	et 
    			inner join dbo.checks oc  on oc.gfno=et.gfno
    			inner join dbo.checkln od  on oc.checkid=od.checkid
    					inner join dbo.invoice im  on et.gfno=im.gfno
    			inner join dbo.invoiceln il  on im.idnum=il.idnum	
     where  et.CloseDate is not null    
     and od.acctcode in (100,200,300,350,400,500)
     or il.acctcode in (100,200,300,350,400,500)
    group by et.gfno,oc.checkdate, im.invoicedate
    
    --select *
    --from #TempDistinctCount
    
    create table #Temp2 (escrowNumber varchar(20),fee_date datetime,titlefee money,escrowfee money, other money, acctcode int,  Row int, id integer identity(1,1) primary key clustered)
    
    insert into #Temp2(escrownumber, fee_date, titlefee, escrowfee, other, acctcode, Row)
    
    select et.gfno
    		,oc.checkdate
    		,sum(case when od.acctcode in (100,200,400)  then od.amount else 0 end)  as titlefee
    		,sum(case when od.acctcode=500  then od.amount else 0 end)   as escrowfee			
    		,sum(case when od.acctcode in (300,350)  then od.amount else 0 end)  as other
    		,case
    				when oc.checkdate is not null
    				then od.acctcode
    		else
    			0
    		end as acctcode
    		,ROW_NUMBER()  OVER (PARTITION BY et.gfno ORDER BY acctcode asc) as ROW
    		from ccwfgnationaltitle.dbo.orders	et (nolock)
    			inner join ccwfgnationaltitle.dbo.checks oc (nolock) on oc.gfno=et.gfno
    			inner join ccwfgnationaltitle.dbo.checkln od (nolock) on oc.checkid=od.checkid
    			inner join @branches b on et.branch=b.branch and et.regid=b.regid
     where oc.checkdate =@today and oc.voiddate is null
    and (oc.payee like 'wfg national title%' or oc.payee like 'transfer to file [A-z]%')
    and oc.checknum=-2
    and et.regid=@regid
    group by et.gfno,oc.checkdate,od.acctcode
    union all
    select et.gfno
    		,im.invoicedate
    		,sum(case when il.acctcode in (100,200,400) then il.amount else 0 end)  as titlefee
    		,sum(case when il.acctcode=500   then il.amount else 0 end)  as escrowfee		
    		,sum(case when il.acctcode in (300,350)  then il.amount else 0 end)  as other
    		,case
    				when im.invoicedate is not null
    				then il.acctcode
    		else
    			0
    		end as AcctCode
    		,ROW_NUMBER()  OVER (PARTITION BY et.gfno ORDER BY acctcode asc) as ROW
    		from ccwfgnationaltitle.dbo.orders et (nolock) 
    			inner join ccwfgnationaltitle.dbo.invoice im (nolock) on et.gfno=im.gfno
    			inner join ccwfgnationaltitle.dbo.invoiceln il (nolock) on im.idnum=il.idnum	
    			inner join @branches b on et.branch=b.branch and et.regid=b.regid		
    			where im.invoicedate = @today
    and isnull(im.initials,'')<>'EST'
    and et.regid=@regid
    and et.branch not in ('OR-Clackamas','OR-Lake Oswego','OR-Sunset','OR-NW Portland','OR-Commercial','OR-Lloyd')
    group by et.gfno,im.invoicedate, il.acctcode
    
    --SELECT *
    --FROM #Temp2
    
    select	@today as today
    		,replace(em.branch,'OR-','') as branch
             ,case when charindex('Residential Sale',og.Description)+charindex('Commercial Sale',og.Description)+charindex('New Construction',og.Description)+charindex('Foreclosure',og.Description)+charindex('Short Sale',og.Description)+charindex('REO',og.Description) > 0  then 'S'
    					when charindex('Residential Refinance',og.Description)+charindex('Commercial Refinance',og.Description)+charindex('Home Equity',og.Description)+charindex('Cash Out',og.Description)+charindex('Residential Refinance NOO',og.Description)+charindex('Residential Refinance Form',og.Description)+charindex('LCLP',og.Description)>0 then 'R'
    						else 'O' 
    					end,'X' as [Type_of_Order]
    			,em.gfno as [Order_No]
    			,em.opendate as [Open_Date]
    			,fees.fee_date as [Closing_Date]
    			,ep.Name as [EO]
    			,tp.Name as [TO]
    			,case when em.kindoforder='T' or af.single1 is not null then 'Title Only'
    					when em.kindoforder='B' and af.single1 is null and af.single2 is null then 'Both'
    					when em.kindoforder='E' and af.single2 is not null then 'Escrow Only'
    					when od.acctcode in (300,350) then 'Other'
    					else 'N/A' end as [Kind_of_Order]
    			,stuff((','+isnull(p.address+' ','')+isnull(p.city,'')
    					+isnull(', '+p.state+' ','')+isnull(p.zip,'')),1,1,'') as [Property_Address]
    			,isnull(bs1.formalname,'') as [Seller]
    			,isnull(la.name+char(10)+char(13),'')+isnull(la.contactfirst+' ','')+isnull(la.contactlast+char(10)+char(13),'')					
    					+isnull(la.Phone,'') as [Listing_Agent]
    			,isnull(sa.name+char(10)+char(13),'')+
    			isnull(sa.contactfirst+' ','')+isnull(sa.contactlast+char(10)+char(13),'')
    					+isnull(sa.phone,'') as [Selling_Agent]
    			,isnull(bs2.formalname,'') as Buyer
    			,isnull(lo.name+char(10)+char(13),'')+isnull(lo.contactfirst+' ','')+isnull(lo.contactlast+char(10)+char(13),'')
    					+isnull(lo.phone,'') as [Mortgage_Broker]				
    			,isnull(bs2.formalname,'') as [Formal_Name]
    			,em.sourceofbusiness as [Source_of_Business]
    			,isnull(c.name+char(10)+char(13),'')+isnull(c.contactfirst+' '+c.contactlast+char(10)+char(13),'')+
    					isnull(c.phone,'')
    			 as [Referral]
    			,em.loanamount
    			,em.salesprice
    			,sales.name as [marketing_rep]
    			,ROW_NUMBER() OVER(PARTITION BY em.GFNo ORDER BY od.acctcode DESC) AS Row
    from ccwfgnationaltitle.dbo.orders em 
    						inner join ccwfgnationaltitle.dbo.checks oc on em.gfno = oc.gfno
    						inner join ccwfgnationaltitle.dbo.checkln od on oc.checkid = od.checkid
    						left join ccwfgnationaltitle.dbo.tomain t on em.tonum=t.tonum and em.regid=t.regid
    						left join ccwfgnationaltitle.dbo.property p on em.gfno=p.gfno
    						left join ccwfgnationaltitle.dbo.seller s on em.gfno=s.gfno and s.sortfield=1
    						left join ccwfgnationaltitle.dbo.buysel bs1 on s.buyersellerid=bs1.buyersellerid
    						left join ccwfgnationaltitle.dbo.buyer b on em.gfno=b.gfno and b.sortfield=1
    						left join ccwfgnationaltitle.dbo.buysel bs2 on b.buyersellerid=bs2.buyersellerid
    						left join ccwfgnationaltitle.dbo.agent la on em.agentseller=la.agentnum
    						left join ccwfgnationaltitle.dbo.agent sa on em.agentbuyer=sa.agentnum
    						left join ccwfgnationaltitle.dbo.agent lo on em.MortgageBrokerID=lo.agentnum	
    						left join ccwfgnationaltitle.dbo.agent c on em.customer=c.agentnum	
    						left join ccWFGNationalTitle.dbo.EscrowProcessor ep on em.CloseAgent=ep.Initials	
    						left join ccwfgnationaltitle.dbo.titleprocessor tp on em.titleofficer=tp.initials
    						left join ccwfgnationaltitle.dbo.salesnames sales on em.salesrep=sales.initials	
    						left join ccwfgnationaltitle.dbo.adfields af on em.gfno=af.gfno
    						left join ccwfgnationaltitle.dbo.ordercategories og on em.ordercategory=og.ordercategory
    						inner join #Temp2 fees on em.gfno=fees.escrownumber
    				where  em.regid=@regid	
    								and Row = 1
    				group by replace(em.branch,'OR-',''),
    							isnull(case when em.tonum is null then 
    				case when charindex('Residential Sale',og.Description)+charindex('Commercial Sale',og.Description)+charindex('New Construction',og.Description)+charindex('Foreclosure',og.Description)+charindex('Short Sale',og.Description)+charindex('REO',og.Description)>0 then 'S'
    						when charindex('Residential Refinance',og.Description)+charindex('Commercial Refinance',og.Description)+charindex('Home Equity',og.Description)+charindex('Cash Out',og.Description)+charindex('Residential Refinance NOO',og.Description)+charindex('Residential Refinance Form',og.Description)+charindex('LCLP',og.Description)>0 then 'R'
    						end
    	when em.ordercategory is not null then case when charindex('Residential Sale',og.Description)+charindex('Commercial Sale',og.Description)+charindex('New Construction',og.Description)+charindex('Foreclosure',og.Description)+charindex('Short Sale',og.Description)+charindex('REO',og.Description)>0 then 'S'
    						when charindex('Residential Refinance',og.Description)+charindex('Commercial Refinance',og.Description)+charindex('Home Equity',og.Description)+charindex('Cash Out',og.Description)+charindex('Residential Refinance NOO',og.Description)+charindex('Residential Refinance Form',og.Description)+charindex('LCLP',og.Description)>0 then 'R'
    						else 'O' 
    					end end,'X')
    			,em.gfno 
    			,em.opendate
    			,fees.fee_date 
    			,ep.name
    			,tp.name
    			,isnull(bs1.formalname,'') 			
    			,isnull(la.name+char(10)+char(13),'')+isnull(la.contactfirst+' ','')+isnull(la.contactlast+char(10)+char(13),'')					
    					+isnull(la.Phone,'') 
    			,isnull(sa.name+char(10)+char(13),'')+
    				isnull(sa.contactfirst+' ','')+isnull(sa.contactlast+char(10)+char(13),'')
    					+isnull(sa.phone,'') 
    			,isnull(bs2.formalname,'') 
    			,isnull(lo.name+char(10)+char(13),'')+isnull(lo.contactfirst+' ','')+isnull(lo.contactlast+char(10)+char(13),'')
    					+isnull(lo.phone,'') 					
    			,isnull(bs2.formalname,'') 
    			,em.sourceofbusiness 
    			,isnull(c.name+char(10)+char(13),'')+isnull(c.contactfirst+' '+c.contactlast+char(10)+char(13),'')+
    					isnull(c.phone,'')
    			,em.loanamount 
    			,em.salesprice 
    			,sales.name 
    			,case when em.kindoforder='T' or af.single1 is not null then 'Title Only'
    					when em.kindoforder='B' and af.single1 is null and af.single2 is null then 'Both'
    					when em.kindoforder='E' and af.single2 is not null then 'Escrow Only'
    					when od.acctcode in (300,350) then 'Other'
    					else 'N/A' end
    			,od.acctcode
    			,Row
    			,em.tonum
    			,og.ordercategory
    			,og.description
    			,em.kindoforder
    			,af.single1
    			,af.single2
    			,em.ordercategory
    			,p.address
    			,p.city
    			,p.state
    			,p.zip
    
    
    DROP TABLE #Temp2
    DROP TABLE #TempDistinctCount

    Any help is appreciated. Thanks!

    Monday, November 18, 2013 11:33 PM

Answers

  • Save the results in to temp table (common table expression CTE) and then select where row=1


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, November 18, 2013 11:41 PM

All replies

  • Save the results in to temp table (common table expression CTE) and then select where row=1


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, November 18, 2013 11:41 PM
  • ROW_NUMBER()  OVER (PARTITION BY et.gfno ORDER BY acctcode asc) as ROW

    As we can see the row is partitioned by "gfno". It is similar to GROUP BY clause however there are differences, refer http://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by

    Therefore multiple "gfno" can have same row number since the row has been partitioned or grouped by "gfno".

    You can try by removing PARTITION BY et.gfno.


    Regards, RSingh


    • Edited by RSingh() Tuesday, November 19, 2013 5:49 AM
    Tuesday, November 19, 2013 5:48 AM
  • GaryT_Musicman,
    Since you have a WHERE clause and the filter is "=1" then it is clearly stated that you are selecting where ROW_NUMBER() is returning 1.

    Now if you are getting 6 rows instead of 1 then probably because your Partition By and Order By clause inside OVER are returning multiple tuples for same value "1".

    Please check your logic once again.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 19, 2013 6:12 AM
  • I am confused (obviously) about the partition by. I thought that the partition by was whatever we wanted to reset tne row numbering by in the function. (How many rows have GFNo 13050521?). I know that multiple GFNo's can have the row number of 1. That's fine. I'm trying to select only the first row of each GFNo for display.

    I chose acctcode for the Order by clause because in the case of multiple rows for a GFNo, acctcode is a value that can not be repeated.

    I tried removing the partition by, but the result set shrinks to one GFNo consisting of multiple rows.

    What am I missing? Is my evaluation of the partition by incorrect?

    Tuesday, November 19, 2013 4:40 PM
  • There is strange query. You seem to be creating this new Row column and immediately using it in the WHERE clause which is impossible.

    Can you please re-post the select statement only and in a way we can see the whole statement without scrolling? You can not use a new column created in the SELECT statement immediately in the WHERE clause unless you use a derived table. But I don't see derived table or CTE in your query.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, November 19, 2013 4:52 PM
    Moderator
  • I wasn't seeing the forest because of all these blasted trees in the way.

    In my final select I was calling the Row_Number() function instead of simply pulling in the row from the CTE.

    When I referenced the Row instead of the function, I get one row per GFNo.

    Thanks all for the tips!

    Tuesday, November 19, 2013 6:03 PM