Aggregate function to filter records

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

Answer 1

Hi ..

You can use this: 

SELECT OrderRef , MIN(CreatedDate) 
 FROM Orders 
 WHERE OrderId = @OrderId
  AND OwnerId = @OwnerId
 GROUP BY OrderRef

-- Reddy Balaji C.

Answer 2

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

Answer 3

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

Answer 4

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

 

Answer 5

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

Answer 6

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

Answer 7

why do you need With Ties here?

---

pn

Answer 8

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

Answer 9

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/

Answer 10

ok Tx

---

pn