Results 1 to 3 of 3

Thread: group by

  1. #1
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96

    Unanswered: group by

    I have table

    date,type,price

    date and price is changing

    i need to group it, to have

    max(date), type and price (from the row with max date)
    it's no problem to take
    max(date) and type bat without the third column, i need all columns

    Help

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    CREATE TABLE myTable99([date] datetime, type char(10), price money
    		, PRIMARY KEY (type, [date]))
    GO
    
    INSERT INTO myTable99([date],type,price)
    SELECT '1/1/2001','bat' ,10.00 UNION ALL
    SELECT '1/1/2002','bat' ,20.00 UNION ALL
    SELECT '1/1/2003','bat' ,30.00 UNION ALL
    SELECT '1/1/2004','bat' ,40.00 UNION ALL
    SELECT '1/1/2001','ball',40.00 UNION ALL
    SELECT '1/1/2002','ball',30.00 UNION ALL
    SELECT '1/1/2003','ball',20.00 UNION ALL
    SELECT '1/1/2004','ball',10.00
    GO
    
    SELECT l.* 
      FROM myTable99 AS l
      JOIN ( SELECT type, MAX([date]) AS MAX_date
    	   FROM myTable99
           GROUP BY type) AS r
        ON l.type = r.type
       AND l.[date] = r.MAX_date
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    Long thanks

Posting Permissions

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