locked
Newbie-Need help with Group By RRS feed

  • Question

  • 	Have query as shown below. Wht I want is to return only the most recent record in each 'Deal' I can accomplish this by returning the records that have the 
    
    largest (MAX?) AlertID when Grouped by DealKey. I tried various approaches but keep getting syntax errors. Please advise. Any help is appreciated . Thank you
    
    SELECT 
    	             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') 
    	ORDER BY tblAlertNegotiations.SentDateTime DESC
    


    Steve Greenbaum
    Sunday, July 31, 2011 11:34 AM

Answers

  • 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
  • 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
  • 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

All replies


  • 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
  • 	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
  • 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
  • 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
  • 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
  • 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
  • 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
  • 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
  • OK Thanks. This was first CTE
    Steve Greenbaum
    Sunday, July 31, 2011 1:27 PM
  • 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
  • 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
  • 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
  • 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