Newbie-Need help with Group By

All replies

• 

  

  0

  Sign in to vote

  WITH cte

  AS

  (

  SELECT      ROW_NUMBER() OVER (PARTITION BY tblOffer.DealKey ORDER BY tblOffer.DealKey ) rn
  	       tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	       tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName, 
  	       tblOffer.DealKey 
  	FROM   tblAlertNegotiations INNER JOIN
  	       tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	       tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID INNER JOIN
  	       tblOffer ON tblAlertNegotiations.OfferID = tblOffer.OfferID
  	WHERE  (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') 

  ) SELECT * FROM cte WHERE rn=1
  	ORDER BY tblAlertNegotiations.SentDateTime DESC
  

  
  

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • Proposed as answer by Naomi N Sunday, July 31, 2011 2:35 PM

  Sunday, July 31, 2011 11:48 AM

  Answerer
• 

  

  0

  Sign in to vote

  	Thank you. Very Interesting. I ran as below but got syntax error 'Incorrect syntax near tblAlertNegotiations'. I tried puttting a comma after rn in first lineof SELECT but then got error

  'The column DealKey was specified multiple times for cte.

  
  

  ALTER PROCEDURE dbo.procGetNegotiationDataByPersonID_NewestPerDealSelect
  	(
  	@personid int	
     )	
    AS
  
  	WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY tblOffer.DealKey ORDER BY tblOffer.DealKey ) rn
  	    tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	    tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName, 
  	    tblOffer.DealKey 
  	FROM  tblAlertNegotiations INNER JOIN
  	    tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	    tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID INNER JOIN
  	    tblOffer ON tblAlertNegotiations.OfferID = tblOffer.OfferID
  	WHERE (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') ) 
  	
  	SELECT * FROM cte WHERE rn=1
  	ORDER BY tblAlertNegotiations.SentDateTime DESC
   
  

  
  
  

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 12:13 PM
• 

  

  0

  Sign in to vote

  It is because  you need to refer to the column defined within a CTE

  Should be

  SELECT * FROM cte WHERE rn=1
  	ORDER BY SentDateTime DESC

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  Sunday, July 31, 2011 12:20 PM

  Answerer
• 

  

  0

  Sign in to vote

  Thank you. I deleted entire 2nd SELECT and ran as below but still get syntax error --Incorrect syntax near tblAlertNegotiations.  Tried comma after rn in first SELECT line but got different error

  ALTER PROCEDURE dbo.procGetNegotiationDataByPersonID_NewestPerDealSelect
  	(
  	@personid int	
     )	
    AS
  
  	WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY tblOffer.DealKey ORDER BY tblOffer.DealKey ) rn
  	    tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	    tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName, 
  	    tblOffer.DealKey 
  	FROM  tblAlertNegotiations INNER JOIN
  	    tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	    tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID INNER JOIN
  	    tblOffer ON tblAlertNegotiations.OfferID = tblOffer.OfferID
  	WHERE (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') ) 
  	
  

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 12:31 PM
• 

  

  1

  Sign in to vote

  See if the below works for you

  ALTER PROCEDURE dbo.procGetNegotiationDataByPersonID_NewestPerDealSelect
  	(
  	@personid int	
    )	
   AS
  
  	;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY tblOffer.DealKey ORDER BY tblOffer.DealKey ) rn,
  	  tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	  tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName, 
  	  tblOffer.DealKey 
  	FROM tblAlertNegotiations INNER JOIN
  	  tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	  tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID INNER JOIN
  	  tblOffer ON tblAlertNegotiations.OfferID = tblOffer.OfferID
  	WHERE (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') ) 

  SELECT * FROM cte WHERE rn=1
  	

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • Marked as answer by steve48 Sunday, July 31, 2011 12:45 PM

  Sunday, July 31, 2011 12:33 PM

  Answerer
• 

  

  0

  Sign in to vote

  Thank you. YES-That worked!!!!. I ran with and without  ";"  in front of WITH.

  Why is it there? Typo?

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 12:47 PM
• 

  

  1

  Sign in to vote

  When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  Sunday, July 31, 2011 1:04 PM

  Answerer
• 

  

  1

  Sign in to vote

  When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

  ---

  Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

  • Marked as answer by steve48 Sunday, July 31, 2011 1:26 PM

  Sunday, July 31, 2011 1:04 PM

  Answerer
• 

  

  0

  Sign in to vote

  OK Thanks. This was first CTE

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 1:27 PM
• 

  

  0

  Sign in to vote

  Hi Again, Thought working but not doing exactly what I wanted,

  I want it to return only the record with the highest AlertID in each partition.

  Sorry if I was not clear.

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 2:09 PM
• 

  

  1

  Sign in to vote

  It can not work because this select statement still lists DealKey twice. Unless you remove one of the DealKey reference in CTE, you will keep getting an error.

  ---

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

  Sunday, July 31, 2011 2:38 PM
• 

  

  1

  Sign in to vote

  We need a very slight change:

  ALTER PROCEDURE dbo.procGetNegotiationDataByPersonID_NewestPerDealSelect
  	(
  	@personid int	
   )	
   AS
  
  	;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY tblOffer.DealKey ORDER BY tblAlertNegotiations.AlertID DESC ) rn,
  	 tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	 tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName, 
  	 tblOffer.DealKey as OfferDealKey -- renaming here to avoid error
  	FROM tblAlertNegotiations INNER JOIN
  	 tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	 tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID INNER JOIN
  	 tblOffer ON tblAlertNegotiations.OfferID = tblOffer.OfferID
  	WHERE (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') ) 
  SELECT * FROM cte WHERE rn=1
  
  

  
  

  ---

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

  • Marked as answer by steve48 Sunday, July 31, 2011 3:56 PM

  Sunday, July 31, 2011 2:40 PM
• 

  

  0

  Sign in to vote

  Thank you. I see that adding DESC to Order By clause ensures that I'll get the most recent one. Very nice. I didn't realize DealKey was in my original table so was able to simplify. to:

  ALTER PROCEDURE dbo.procGetNegotiationDataByPersonID_NewestPerDealSelect
  	(
  	@personid int,
  	@onlynew bit ='False' 
   )	
   AS
  
  	;WITH cte AS ( SELECT ROW_NUMBER() OVER (PARTITION BY tblAlertNegotiations.DealKey ORDER BY tblAlertNegotiations.DealKey DESC ) rn,
  	 tblAlertNegotiations.AlertID, tblAlertNegotiations.DealKey, tblAlertNegotiations.ToPersonID, tblAlertNegotiations.AlertText, tblAlertNegotiations.OfferID, 
  	 tblAlertNegotiations.SentDateTime, pto.FName, pto.LName, pto.FName + ' ' + pto.LName AS ToFullname, pfrom.FName + ' ' + pfrom.LName AS FromFullName 
  	
  	FROM tblAlertNegotiations INNER JOIN
  	 tblPerson AS pto ON tblAlertNegotiations.ToPersonID = pto.PersonID INNER JOIN
  	 tblPerson AS pfrom ON tblAlertNegotiations.FromPersonID = pfrom.PersonID 
  	WHERE (tblAlertNegotiations.ToPersonID = @personid) AND (tblAlertNegotiations.RespondedTo = '0') ) 
  	
  	SELECT * FROM cte WHERE rn=1
  

  ---

  Steve Greenbaum

  Sunday, July 31, 2011 3:59 PM