Select Must Return only one row against every id-CodePudding

I'm using the below query to get results. The purpose of the query is to get the latest sales_amount of every customer, but when the sales are two or more in the given date range, the query returns all the records, how I can get only the latest records against the id. The same id should contain only one row against each id.

SELECT id,
       Max(date),
       sales_amount
FROM   customer
WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
       AND id = 1001
GROUP  BY id,
          sales_amount;

CodePudding user response：

this is because you have included the ID in teh group by clause, all ID's will be returned.

CodePudding user response：

Have you tried adding:

   SELECT id,
   Max(date),
   sales_amount
   FROM   customer
   WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
   AND id = 1001
   GROUP  BY id,
      sales_amount
   ORDER BY date DESC 
   LIMIT 1;

CodePudding user response：

You can use row number in a sub query to give you an ordering and then just pick the first one.

SELECT *
FROM (
  SELECT id, date, sales_amount,
         ROW_NUMBER() OVER (ORDER BY date DESC) as RN
  FROM   customer
  WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
       AND id = 1001
) sub
WHERE RN = 1

Note if you want to do it for all customers then this is the query

SELECT *
FROM (
  SELECT id, date, sales_amount,
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) as RN
  FROM   customer
  WHERE  date BETWEEN '2020-08-01' AND '2020-08-15'
) sub
WHERE RN = 1

That will give you the most recent row for each customer.