Select a field based on aggregate of another - very frustrating!!!!

[NixyJ]

Hi All,

Happy 2007 Tek-Tippers!!

I have a generic "is it possible?" SQL query. If possible I will need to use any solutions in postgreSQL and SQL Server 2000.

I have a table that contains Order information - let's call it tblOrders for the sake of originality. Every row represents a unique order, and contains order_number, order_date and customer_id.

What I want to do is write a query that will return 1 row for every customer with the order_number of their 1st order. Unfortunately order_number is not always sequential so i can't take min(order_number). I need to return the order_number for min(order_date) for each customer.

I have tried anbsolutely every combo of WHERE, GROUP BY and HAVING clauses, and our internal guru's are saying they doubt it can be done without a stored proc - I jsut can't believe it can't be done!!

Please, if anyone can possibly help, or confirm for certain that it can't be done, I will sleep a lot easier!!

Many thanks to anyone who can help,
NixyJ x

 

[gmmastros]

Have you tried using a subquery joined back to the orders table, like this...

Select tblOrders.*
From   tblOrders
       Inner Join (
         Select Customer_Id, Min(Order_Date) As MinOrderDate
         From   tblOrders
         Group By Customer_ID
         ) As FirstOrder
         On tblOrders.Customer_ID = FirstOrder.Customer_ID
         And tblOrders.order_date = FirstOrder.MinOrderDate

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[LKBrwnDBA]

-- OR --

Select *
  From   tblOrders O
 Where   Order_Date = (
  Select Min(Order_Date) 
    From tblOrders d
   Where O.Customer_ID = D.Customer_ID)

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[NixyJ]

Hi there,

Thanks for your speedy responses! i was trying to avoid having to link on a timestamp field as I wasn't suer how robust that would be - maybe I'm just being overly tentative - they're kind of my bette noir!!

Anyway, I greatly appreciate you taking the time to answer - I'll give these both a go and see if there are any benefits either way. Do you think one is significantly heavier than the other?

Thanks once again,
Nx

 

[r937]

bette noir was a film star back in the black-and-white days

you're thinking of bête noire

;-)

r937.com | rudy.ca