Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: SELECT question, grouping values

    What is the best way to build a SQL query to extract data from a table in this manner:

    COLA COLB COLC DATE
    01 01 01 04/15/1988
    01 01 01 11/23/1997
    01 01 01 02/02/2005
    01 02 01 6/14/1990
    01 02 01 10/30/1999
    01 02 02 4/24/2001

    How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?)

    Thanks!!!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use a subquery to get the most recent record dates, and then join this back to your table to get the complete record:
    Code:
    select	[YourTable].COLA,
    	[YourTable].COLB,
    	[YourTable].COLC,
    	[YourTable].DATE,
    	[YourTable].[Any other fields...]
    from	[YourTable]
    	inner join --LatestRecords
    		(select	COLA,
    			COLB,
    			COLC,
    			Max(DATE) as DATE
    		from	[YourTable]
    		group by COLA,
    			COLB,
    			COLC) LatestRecords
    		on [YourTable].COLA = LatestRecords.COLA
    		and [YourTable].COLB = LatestRecords.COLB
    		and [YourTable].COLC = LatestRecords.COLC
    		and [YourTable].DATE = LatestRecords.DATE
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •