Solved

mysql find the most recent  DISTINCT  customers from multiple orders

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

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now