mysql find the most recent DISTINCT customers from multiple orders

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)
willsherwoodAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dan CraciunIT ConsultantCommented:
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
willsherwoodAuthor Commented:
sorry i was indeed imprecise,
the five customers with the most recent orders.
0
Dan CraciunIT ConsultantCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

willsherwoodAuthor Commented:
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
Dan CraciunIT ConsultantCommented:
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
willsherwoodAuthor Commented:
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
Dan CraciunIT ConsultantCommented:
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
willsherwoodAuthor Commented:
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
Dan CraciunIT ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
regmigrantCommented:
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
Dan CraciunIT ConsultantCommented:
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
willsherwoodAuthor Commented:
(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
willsherwoodAuthor Commented:
many thanks!
0
Dan CraciunIT ConsultantCommented:
Glad I could help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.