locked
Aggregate function to filter records RRS feed

  • Question

  • Table: Orders
    Fields: OrderId, OwnerId, orderRef, createdDate

    Given OrderId and OwnerId i need to get the OrderRef having Min(CreatedDate).
    How can this be done?


    pn
    Friday, August 6, 2010 5:13 PM

Answers

  • You can't group by OrderRef.

    Data is going to be like this,

    OrderId OwnerId OrderRef CreatedDate

    100        100         A1          2010-08-06 11:42:00

    100        100         A3          2010-08-06 11:41:00

     

    i want the query to return A3.


    pn


    Try:

    SELECT TOP (1) WITH TIES OrderRef, CreatedDate FROM Orders WHERE OrderID = @OrderID AND OwnerID = @OwnerID ORDER BY CreatedDate;

     

    AMB

    • Proposed as answer by Naomi N Friday, August 6, 2010 5:47 PM
    • Marked as answer by KJian_ Friday, August 13, 2010 7:18 AM
    Friday, August 6, 2010 5:37 PM

All replies

  • Hi ..

    You can use this: 

    SELECT OrderRef , MIN(CreatedDate) 
     FROM Orders 
     WHERE OrderId = @OrderId
      AND OwnerId = @OwnerId
     GROUP BY OrderRef
    
    -- Reddy Balaji C.

    Friday, August 6, 2010 5:21 PM
  • You can't group by OrderRef.

    Data is going to be like this,

    OrderId OwnerId OrderRef CreatedDate

    100        100         A1          2010-08-06 11:42:00

    100        100         A3          2010-08-06 11:41:00

     

    i want the query to return A3.


    pn
    Friday, August 6, 2010 5:26 PM
  • Can you post table schema, including constraints and indexes, sample data in the form of "insert" statements, and expected result?

    I think I am missing something in the description of this problem, like is OrderID unique in this table, or what is the relation between OrderRef and OrderID, etc.

    AMB

    Friday, August 6, 2010 5:27 PM
  • The unique key is OwnerId,OrderId,OrderRef.

    OwnerId and OrderId can be duplicates but orderref will be unique for those records

     

    OrderId OwnerId OrderRef CreatedDate

    100        100         A1          2010-08-06 11:42:00

    100        100         A3          2010-08-06 11:41:00

    101       150         A1         2010-08-06 11:41:00

    101       150         A5        2010-08-06 11:50:00

     

    Given the OrderId and OwnerId i need to get the oldest OrderRef.

    If OrderId = 100 and OwnerId is 100 then orderRef returned should be A3

     


    pn

     

    Friday, August 6, 2010 5:29 PM
  • You can't group by OrderRef.

    Data is going to be like this,

    OrderId OwnerId OrderRef CreatedDate

    100        100         A1          2010-08-06 11:42:00

    100        100         A3          2010-08-06 11:41:00

     

    i want the query to return A3.


    pn


    Try:

    SELECT TOP (1) WITH TIES OrderRef, CreatedDate FROM Orders WHERE OrderID = @OrderID AND OwnerID = @OwnerID ORDER BY CreatedDate;

     

    AMB

    • Proposed as answer by Naomi N Friday, August 6, 2010 5:47 PM
    • Marked as answer by KJian_ Friday, August 13, 2010 7:18 AM
    Friday, August 6, 2010 5:37 PM
  • Please read these two blogs that give you explanation of the problem and various solutions:

     

    Including an Aggregated Column's Related Values
    Including an Aggregated Column's Related Values - Part 2
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 6, 2010 5:48 PM
  • why do you need With Ties here?
    pn
    Friday, August 6, 2010 5:59 PM
  • In case you have multiple records with the same date and want to return all of them.
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Friday, August 6, 2010 6:01 PM
  • Here are a few other methods.

    --SQL 2005+
    ;WITH cte
    AS
    (
    SELECT *,ROW_NUMBER() OVER(PARTITION BY OrderId,OwnerId ORDER BY CreatedDate ASC) AS seq
    FROM @t
    WHERE 
    	OrderId = 100
    	AND OwnerId = 100
    )
    SELECT *
    FROM [cte]
    WHERE seq = 1
    
    --SQL 2000
    SELECT o1.*
    FROM @t o1
    INNER JOIN(
    	SELECT OrderId,OwnerId,MIN(CreatedDate) AS MinDt
    	FROM @t
    	WHERE OrderId = 100 AND OwnerId =100
    	GROUP BY OrderId,OwnerId
    ) AS o2 ON [o1].OrderId = o2.OrderId AND o1.OwnerId = o2.OwnerId AND o1.CreatedDate = o2.[MinDt]
    WHERE 
    	o1.OrderId = 100
    	AND o1.OwnerId = 100
    

    http://jahaines.blogspot.com/
    Friday, August 6, 2010 6:06 PM
  • ok Tx
    pn
    Friday, August 6, 2010 6:10 PM