Solved

mysql find the most recent  DISTINCT  customers from multiple orders

Posted on 2014-02-28
14
338 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
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 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

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.

Question has a verified solution.

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

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Creating and Managing Databases with phpMyAdmin in cPanel.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

776 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