469,126 Members | 1,255 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,126 developers. It's quick & easy.

Select Most Recent Record for all Customer IDs

What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery.

I have this so far:
Expand|Select|Wrap|Line Numbers
  1. SELECT OrderOrAdjust, CustomerID,OrderDate,PaymentStatus,PaymentMethod from bi_extract
  2. WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
  3. GROUP BY CustomerID 
  4. ORDER BY CustomerID asc, CustomerFirstOrderDate desc
and I get this error:
column'bi_extract.OrderOrAdjust' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

What am I doing wrong?

JP
May 29 '07 #1
1 3904
Whenever you have a GROUP BY clause, then everything in the SELECT clause must reference only columns in this list, or be an aggregate.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(OrderOrAdjust), CustomerID,MAX(OrderDate), MAX(PaymentStatus),MAX(PaymentMethod) from bi_extract
  2. WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
  3. GROUP BY CustomerID 
  4. ORDER BY CustomerID asc, CustomerFirstOrderDate desc
or:
Expand|Select|Wrap|Line Numbers
  1. SELECT OrderOrAdjust, CustomerID,MAX(OrderDate), PaymentStatus,PaymentMethod from bi_extract
  2. WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
  3. GROUP BY OrderOrAdjust, CustomerID,PaymentStatus,PaymentMethod 
  4. ORDER BY CustomerID asc, CustomerFirstOrderDate desc

What I'm trying to do is select the most recent record for each customer who created an account between 2004-01-01 and 2007-01-01. I'm using MSQuery.

I have this so far:
Expand|Select|Wrap|Line Numbers
  1. SELECT OrderOrAdjust, CustomerID,OrderDate,PaymentStatus,PaymentMethod from bi_extract
  2. WHERE CustomerFirstOrderDate >=2004/01/01 AND CustomerFirstOrderDate <2007/01/01 AND OrderOrAdjust='order' AND PaymentStatus='payment approved' AND (PaymentMethod='VISA' OR PaymentMethod='MasterCard' OR PaymentMethod='American Express' OR PaymentMethod='Cash' OR PaymentMethod='Purchase Order')
  3. GROUP BY CustomerID 
  4. ORDER BY CustomerID asc, CustomerFirstOrderDate desc
and I get this error:
column'bi_extract.OrderOrAdjust' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

What am I doing wrong?

JP
May 29 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by mr_burns | last post: by
1 post views Thread by Rowan | last post: by
3 posts views Thread by William Wisnieski | last post: by
reply views Thread by liz82much | last post: by
radcaesar
reply views Thread by radcaesar | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.