Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fafful
Starting Member
4 Posts |
 Posted - 2003-02-21 : 04:57:45
|
| Hi All,Looking at the following sample Data held in one table Table_A, how can I find the latest record for a customer who places orders for products based on Year and then datetime fields. The customers can place different orders in one single day and also on different days as well as historically orders placed in different years. I tried to use the maximum function but got stuck.Cust_id(VarChar) order_id(Varchar) Year(varchar) Datetime(datetime)A1001 00100 1991 02/02/1991 00.00.10A1001 00101 1991 02/02/1991 00.02.00B1002 00102 2001 20/10/2001 10.10.00B1003 00103 2002 25/11/2002 02.18.02 B1003 00104 2002 18/12/2002 13.14.04B1003 10105 2001 22/08/2001 00.00.00 Thanks FA |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-02-21 : 07:20:21
|
| It's clear to me that you need to use a GROUP BY clause and a MAX() aggregate; however, it is unclear if you are looking for the latest order each customer made each year or the latest order each customer made ever.Why do you store the year twice in your table?Jay White{0} |
 |
|
|
fafful
Starting Member
4 Posts |
Posted - 2003-02-21 : 08:33:12
|
| HiI am looking for the latest record ever in the whole table for the customer.The Year is stored twice, but the year part in the Datetime field is not dependant on the year in the year field. So in effect there could be for thesame Year 3 different orders for the customer with 3 diferent Datetime fields. So like I said, I would like to have the record for the customer that has the latest Year and within that which record has the latest Datetime, grouping by Customer.Thanks FA |
|
|
|
|
|
|
Query for latest record or occurence