• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Select DISTINCT(cust_nbr), MAX(cmplt_dt)

Select DISTINCT(cust_nbr), MAX(cmplt_dt)
FROM sales_datebook, ui_results_db.emp1
where  cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'  
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt

XXXXXXX      5/24/2006
XXXXXXX      6/29/2006
XXXXXXX      7/12/2006
XXXXXXX      8/25/2006
XXXXXXX      8/31/2006
XXXXXXX      10/2/2006
XXXXXXX      10/6/2006
XXXXXXX      10/17/2006

The above query results in the above list, which is the correct list.
However, my need is for only the LAST or LATEST date that the account was called, not all dates.
The DISTRICT() and MAX() seem to have no effect...+
Can someone help? Thanks!!!~bat
0
ba_trainer
Asked:
ba_trainer
1 Solution
 
gpompeCommented:
Try this:

Select cust_nbr, MAX(cmplt_dt)
FROM sales_datebook, ui_results_db.emp1
where  cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'  
AND emp_nbr = emp1
group by cust_nbr
0
 
srafi78Commented:
Select cust_nbr, cmplt_dt
FROM sales_datebook, ui_results_db.emp1
where  cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'  
AND emp_nbr = emp1
AND cmplt_dt = (Select Max(cmplt_dt) from
sales_datebook, ui_results_db.emp1
where  cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'  
AND emp_nbr = emp1)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT Cust_nbr, Max(cmplt_dt)cmplt_dt
FROM (
Select DISTINCT(cust_nbr), MAX(cmplt_dt) cmplt_dt
FROM sales_datebook, ui_results_db.emp1
where  cust_nbr IS NOT NULL
AND cmplt_dt > '05/14/2006'  
AND emp_nbr = emp1
group by cust_nbr, cmplt_dt
)T
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now