[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

Chose the highest value

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
0
babak62
Asked:
babak62
  • 3
  • 2
1 Solution
 
Walter RitzelSenior Software EngineerCommented:
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

0
 
babak62Author 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?
0
 
Aaron TomoskyTechnology ConsultantCommented:
You ate grouping by timestamp instead of c.customer_id
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Walter RitzelSenior Software EngineerCommented:
Yes, that's your mistake. Change your group by to customerid.
0
 
Walter RitzelSenior Software EngineerCommented:
Yes, that's your mistake. Change your group by to customerid.
0
 
babak62Author Commented:
Great Help
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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