Count in SQL query issue

Question

Want to select only 2 latest records from orders table, where count of AGENT_CODE is less than 3,

Guide me to do this in efficient way, I don’t want to go through all the records whenever this query runs.

My SQL query is something like this but does not work

SELECT ORD_NUM, ORD_DATE
FROM ORDERS
WHERE ADVANCE_AMOUNT>50
AND ( SELECT ORD_DATE, COUNT( AGENT_CODE )
    FROM ORDERS
    HAVING COUNT( AGENT_CODE ) < 3
    ORDER BY ORD_DATE DESC LIMIT 5;)
ORDER BY ORD_DATE DESC LIMIT 2;

My ORDERS table is

CREATE TABLE `orders` (
  `ORD_NUM` int(11) NOT NULL,
  `ADVANCE_AMOUNT` int(11) NOT NULL,
  `ORD_DATE` date NOT NULL,
  `CUST_CODE` varchar(10) NOT NULL,
  `AGENT_CODE` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `orders` (`ORD_NUM`, `ADVANCE_AMOUNT`, `ORD_DATE`, `CUST_CODE`, `AGENT_CODE`) VALUES
(1, 3500, '2017-08-15', 'C00002', 'A08'),
(2, 2500, '2017-09-16', 'C00003', 'A08'),
(3, 500, '2017-07-20', 'C00023', 'A08'),
(4, 4000, '2017-09-16', 'C00007', 'A10'),
(5, 1500, '2017-09-23', 'C00008', 'A04'),
(6, 2500, '2017-07-30', 'C00025', 'A10'),
(7, 4000, '2017-02-15', 'C00008', 'A11'),
(8, 1500, '2017-05-15', 'C00021', 'A11'),
(9, 2500, '2017-12-18', 'C00025', 'A11');

My intention is to select these

   ORD_NUM ORD_DATE
---------- ---------
       5   2017-09-23
       4   2017-09-16

nacho · Accepted Answer · 2017-10-26 09:29:35Z

1

You may try with this:

SELECT ORD_NUM, ORD_DATE
FROM ORDERS
WHERE ADVANCE_AMOUNT>50
group by ord_num, ord_date
having count(agent_code)<3
ORDER BY ORD_DATE DESC LIMIT 2;

EDIT Sorry, try with this one:

SELECT ORD_NUM, ORD_DATE
FROM ORDERS as a
WHERE ADVANCE_AMOUNT>50
and (select count(agent_code)
     from orders as b
     where a.agent_code=b.agent_code)<3
ORDER BY ORD_DATE DESC LIMIT 2;

edited Oct 26, 2017 at 9:29

answered Oct 26, 2017 at 7:14

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Carmine Tambascia Over a year ago

As I wrote to the answer belove, it may "work" in terms of the question "per se" I mean that DMP asked only for the ord_num 5 and 5. But as belove I believe the question is regarding more about the "logic" of have AGENT_CODE that are < 3. In these terms that "LIMIT 2" is not just filtering any other record present in n instance, where n = 1, 2 as for example (10, 4000, '2017-09-16', 'C00007', 'A12');. indeed have a look at what your code does : sqlfiddle.com/#!9/06d3ec/22 and what instead I mean (without the order by ) sqlfiddle.com/#!9/06d3ec/21

Carmine Tambascia Over a year ago

sorry my bad. The query that works in a more general case should be without LIMIT and Group by as well as

nacho Over a year ago

@Carmine Tambascia The LIMIT 2 it's important for DMP just because he is asking for the last 2, not for all. And if you group by agent_code, you lose the information about the date

Carmine Tambascia Over a year ago

indeed I did not post an answer as I believe yours is complete as well as. My comment came more thinking about what really DMP want. As you wrote, he he want the last 2 then yours is perfect, nevertheless instead if his implicit question was about any group agent_code > 3, then that's way I made my comment. Also regarding the group you are right, indeed see my previous comment

Carmine Tambascia Over a year ago

Actually I checked again and I added a record with only one instance, and indeed that can be "filtered" only with a last group by as from here sqlfiddle.com/#!9/06d3ec/36. But again not limit. I am not sure he is asking only for the "last" 2 in terms of date, otherwise then also the record with 'A08' should be included. Instead what I understood is that he want those where agent_code is < 3 in a performance way, without to touch all record(but I will stay to the logic and then resolve the performance, if possible)

|

Barmar · Accepted Answer · 2017-10-26 07:41:03Z

1

Join with a subquery that gets the agent codes with count less than 3:

SELECT ORD_NUM, ORD_DATE
FROM ORDERS AS o
JOIN (SELECT agent_code
      FROM orders
      GROUP BY agent_code
      HAVING COUNT(*) < 3) AS a
ON o.agent_code = a.agent_code
WHERE ADVANCE_AMOUNT>50
ORDER BY ORD_DATE DESC
LIMIT 2

DEMO

answered Oct 26, 2017 at 7:41

Barmar

789k57 gold badges555 silver badges669 bronze badges

5 Comments

Carmine Tambascia Over a year ago

But in case there will be a more general case as I guess, like another record with coun<3 as (10, 4000, '2017-09-16', 'C00007', 'A12'), then the LIMIT 2 statment will just ignore it. better add before the last order by another GROUP BY o.AGENT_CODE

Barmar Over a year ago

What's wrong with ignoring it? The question says that only the 2 most recent orders should be returned, not just one per agent.

Carmine Tambascia Over a year ago

Well in terms of what I got it is not about the most recent, otherwise 'A08' with ord_num =2 should be also picked. Instead my understanding is that he want those with count< 3. Your logic works of course with his example, but adding another record with only one instance, then it will fail, as from here sqlfiddle.com/#!9/06d3ec/36. This only because we provide answer not only for the sake of the question, but for future reference.

Barmar Over a year ago

@CarmineTambascia A08 has count = 3, ord_num = 1, 2, 3, so none of them are included because it should only include agents with less than 3 orders.

Barmar Over a year ago

@CarmineTambascia Maybe what you're talking about is what to do if there are ties in the most recent N rows that are selected? LIMIT 2 will ignore some of them randomly, which might not be desired.

J.Doe · Accepted Answer · 2017-10-26 14:34:06Z

0

Keep in mind that whatever you put after an AND operator must be a boolean value:

AND (whatever condition with true/false value)

If you do not want go through all the records whenever this query runs, use an auxiliary table with entries that match the condition

HAVING COUNT( AGENT_CODE ) < 3

Use a trigger to update this aux table whenever you make an insert value to the original table. https://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html

edited Oct 26, 2017 at 14:34

answered Oct 26, 2017 at 7:29

J.Doe

112 bronze badges

Comments

SQL_M · Accepted Answer · 2017-10-26 07:23:56Z

-1

WITH CTE AS

(  
    SELECT *
    FROM ORDERS
    WHERE ADVANCE_AMOUNT>50
)

SELECT ORD_NUM, ORD_DATE FROM CTE
GROUP BY AGENT_CODE
HAVING COUNT(*) < 3
ORDER BY ORD_DATE DESC LIMIT 2;

answered Oct 26, 2017 at 7:23

SQL_M

2,4852 gold badges18 silver badges32 bronze badges

2 Comments

Barmar Over a year ago

MySQL doesn't have CTE.

Dr Rob Lang Over a year ago

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Collectives™ on Stack Overflow

Count in SQL query issue

4 Answers 4

6 Comments

5 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related