Solved

mysql find the most recent  DISTINCT  customers from multiple orders

Posted on 2014-02-28
14
347 Views
Last Modified: 2014-03-01
i've tried GROUP BY and DISTINCT  but cannot find the right solution to
select the FIVE MOST RECENT CUSTOMERS  where there are multiple orders from each CUSTOMER.

here's my latest try:   (but yields the wrong results)

SELECT  DISTINCT CUS.CustID, CUS.Name FROM tblOrders ORD, tblCustomers CUS WHERE CUS.CustID=ORD.CustID  ORDER BY ORD.ID DESC LIMIT 5

(need the two results: CustID, Name   for each of the recent distinct five)
0
Comment
Question by:willsherwood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39896760
Why not:
SELECT  CustID, Name FROM tblCustomers ORDER BY CustID DESC LIMIT 5

Open in new window


You only need the five most recent customers, right? Why involve the Orders table?

HTH,
Dan
0
 

Author Comment

by:willsherwood
ID: 39897161
sorry i was indeed imprecise,
the five customers with the most recent orders.
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39897314
OK, try this:
SELECT  CustID, Name FROM tblCustomers
    WHERE CustID IN (
        SELECT DISTINCT tblOrders.CustID FROM tblOrders 
        ORDER BY tblOrders.CustID DESC LIMIT 5)

Open in new window

This will first select the customer ids for the most recent orders. Distinct is there so if a customer has multiple orders it will still be counted as 1.
Then it will give you the names of those customers.

HTH,
Dan
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:willsherwood
ID: 39897340
close, very close -
any way to do this in version 5.3?

i get the error    (1235)  THis version of MySQL doesn't yet support LIMIT & IN subquery

thanks for your patience!
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39897357
OK, let's try it with a Join
SELECT DISTINCT tblOrders.CustID, tblCustomers.Name, tblOrders.ID
    FROM tblOrders  JOIN tblCustomers 
        ON tblOrders.CustID = tblCustomers.ID
    ORDER BY tblOrders.ID DESC LIMIT 5

Open in new window

0
 

Author Comment

by:willsherwood
ID: 39897440
thank you for your continued help!
i'm not that familiar with JOIN, but have tried it.

my table Orders is actually called Chunk (an inherited name!)
as a check, the following works:  SELECT tblChunk.CustID FROM tblChunk

but  this error comes about when i execute the following query.
error:
SQL Error (1054): Unknown column 'tblChunk.CustID' in 'on clause'

query (based on your suggestion, which seems like it should work) :
SELECT  DISTINCT CUS.CustID, CUS.Name, CH.ChunkID
   FROM  tblChunk CH JOIN tblCustomers CUS
   ON tblChunk.CustID = tblCustomers.CustID
   ORDER BY tblChunk.ChunkID DESC LIMIT 5
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39897448
OK, if you insist on aliasing your tables:
SELECT DISTINCT CH.CustID, CUS.Name, CH.ChunkID
    FROM tblChunk CH JOIN tblCustomers CUS
        ON CH.CustID = CUS.CustID
    ORDER BY CH.ChunkID DESC LIMIT 5

Open in new window

If it gives the same error (unknown CH.CustID) it simply means you do not have a CustID field on your chunk table, so post here the create statement for your two tables so we can do a correct query.
0
 

Author Comment

by:willsherwood
ID: 39897475
this one works (also see that i confirmed the column field existence in previous post)
however it is not distinct for Customers
result:
CustID      Name      ChunkID
229              Casm      4015
251              Thor      4014
251              Thor      4013
240              Sher       4012
240              Sher       4011

suggestions?  (again thanks)
0
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39897554
Try this:
SELECT DISTINCT CH.CustID, CUS.Name
   FROM tblCustomers CUS
    RIGHT JOIN (
        SELECT DISTINCT tblChunk.CustID
        FROM tblChunk
        ORDER BY tblChunk.ChunkId DESC 
        LIMIT 5
    )CH ON CH.CustID = CUS.CustID

Open in new window

0
 
LVL 19

Expert Comment

by:regmigrant
ID: 39897610
I've been following this with some interest because I can't think of a way that will work. I'd be surprised is Dan's last response handles a situation where the last 5 orders were placed by only two customers for example.

What I am struggling with is that we may need only 5 orders (each customer placed one) or 25 (each customer placed 5). I think the solution may need a combination of group by and having to identify those that placed multiple orders with a union to the last 25 orders placed but my sql is not good enough to figure it out - Dan?

on other hand maybe the above works and I'll get back in my box
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39897616
Tested using this:
CREATE TABLE IF NOT EXISTS `tblchunk` (
  `ChunkId` int(10) unsigned NOT NULL,
  `CustID` int(10) unsigned NOT NULL,
  `Order` varchar(10) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`ChunkId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `tblchunk`
--

INSERT INTO `tblchunk` (`ChunkId`, `CustID`, `Order`) VALUES
(4011, 240, 'aaa'),
(4012, 240, 'gagsa'),
(4013, 251, 'hasha'),
(4014, 251, 'hashg'),
(4015, 229, 'gsdhas'),
(5, 0, ''),
(6, 1, '');


CREATE TABLE IF NOT EXISTS `tblcustomers` (
  `CustID` int(10) unsigned NOT NULL,
  `Name` varchar(50) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`CustID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

--
-- Dumping data for table `tblcustomers`
--

INSERT INTO `tblcustomers` (`CustID`, `Name`) VALUES
(229, 'Casm'),
(251, 'Thor'),
(240, 'Sher'),
(0, 'fdhgasdg'),
(1, 'tyo'),
(2, 'sagsd'),
(3, 'kfg');

Open in new window

0
 

Author Comment

by:willsherwood
ID: 39897646
(Sorry i was out and didn't respond immediately.)

YES Dan's last one works!   Thank you all for your VERY PATIENT help.
this is inspiration for me to dig in more to learn the subtle details of SQL
again MUCH APPRECIATED!
0
 

Author Closing Comment

by:willsherwood
ID: 39897647
many thanks!
0
 
LVL 35

Expert Comment

by:Dan Craciun
ID: 39897650
Glad I could help!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question