Solved

mysql find the most recent  DISTINCT  customers from multiple orders

Posted on 2014-02-28
14
340 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
  • 7
  • 6
14 Comments
 
LVL 34

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 34

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 34

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 34

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 34

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 34

Expert Comment

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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

840 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