We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Chose the highest value

babak62
babak62 asked
on
Medium Priority
401 Views
Last Modified: 2012-05-11
Hi;
I am trying to find customers with thier  last day of purchase and current balance below 1000 and this is my query

WHERE
(c.reg_timestamp) BETWEEN '2011-04-01 00:00:00' AND '2011-04-15 23:59:59' AND o.current_playchips < 10000
ORDER BY  u.USERID ASC , c.reg_timestamp DESC

the problem is this can generate 3 results for some customers and I need the last last date they purchased so is there anyway that I can get the  HIGHEST c.reg_timestamp or time stamp in this where clause also only the last date the customer has purchased?  c.reg_timestamp iis in date format so I hope there is a function which can select the highest date among more than 1 results per customer.
Thanks for the hielp
Comment
Watch Question

Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
you should use the MAX(c.reg_timestamp) on your SELECT clause witha GROUP BY, like this;

Select c.customer_id, max(c.reg_timestamp)
from c, o, u
WHERE
(c.reg_timestamp) BETWEEN '2011-04-01 00:00:00' AND '2011-04-15 23:59:59' AND o.current_playchips < 10000
group by c.customer_id
ORDER BY  u.USERID ASC , c.reg_timestamp DESC

Author

Commented:
I changed it to this

SELECT
u.USERID,
p.item_price,
s.`name`,
o.current_playchips,
c.`status`,
max(o.reg_timestamp),
c.transaction_id
from t_purchase_skyypay o
inner join t_purchase_skyypay_confirmed c on o.id = c.order_id
inner join t_purchase_products p on p.id = o.item_id
inner join t_purchase_processor s on s.id = o.processor_id
inner join t_user u on u.id = o.user_id
WHERE
(o.reg_timestamp) BETWEEN '2011-04-25 00:00:00' AND '2011-04-28 23:59:59' AND o.current_playchips < 10000
group by c.reg_timestamp
ORDER BY  u.USERID ASC , c.reg_timestamp DESC

but I am still getting multiple records from one customer.  Would you please see what am I doing wrong?
Aaron TomoskyDirector, SD-WAN Solutions
CERTIFIED EXPERT

Commented:
You ate grouping by timestamp instead of c.customer_id
Senior Software Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Walter RitzelSenior Software Engineer
CERTIFIED EXPERT

Commented:
Yes, that's your mistake. Change your group by to customerid.

Author

Commented:
Great Help
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.