willsherwood
asked on
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)
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)
ASKER
sorry i was indeed imprecise,
the five customers with the most recent orders.
the five customers with the most recent orders.
OK, try this:
Then it will give you the names of those customers.
HTH,
Dan
SELECT CustID, Name FROM tblCustomers
WHERE CustID IN (
SELECT DISTINCT tblOrders.CustID FROM tblOrders
ORDER BY tblOrders.CustID DESC LIMIT 5)
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
ASKER
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!
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!
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
ASKER
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
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
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
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.
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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');
ASKER
(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!
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!
ASKER
many thanks!
Glad I could help!
Open in new window
You only need the five most recent customers, right? Why involve the Orders table?
HTH,
Dan