Sorting date from a table

Question

I have the following table in PostgreSQL and I want to display the maximum sale figure of each fname along with the relevant saledate. Please help me with the solution code.

CREATE TABLE CookieSale (
  ID VARCHAR(4),
  fname VARCHAR(15),
  sale FLOAT,
  saleDate DATE
);

INSERT INTO CookieSale
VALUES
('E001', 'Linda', 1000.00, '2016-01-30'),
('E002', 'Sally', 750.00, '2016-01-30'),
('E003', 'Zindy', 500.00, '2016-01-30'),
('E001', 'Linda', 150.00, '2016-02-01'),
('E001', 'Linda', 5000.00, '2016-02-01'),
('E002', 'Sally', 250.00, '2016-02-01'),
('E001', 'Linda', 250.00, '2016-02-02'),
('E002', 'Sally', 150.00, '2016-02-02'),
('E003', 'Zindy', 50.00, '2016-02-02');

I tried with

SELECT fname, MAX(sale), saleDate 
FROM CookieSale;

I need the results to be like

"Lynda | 5000.00 | 2016-02-01"

Please consider adding some explanation and details to your answer and show us what have you tried that we could look. — Foued MOUSSI, Jan 26 '20 at 10:17
I tried with SELECT fname, MAX(sale), saleDate FROM CookieSale; — madusha tissera, Jan 26 '20 at 10:19
I need the results to be like "Lynda | 5000.00 | 2016-02-01" — madusha tissera, Jan 26 '20 at 10:27

score 0 · Answer 1 · answered Jan 26 '20 at 18:49

Your description and expected results are inconsistent. Description says "maximum sale figure of each fname" but expected results indicates only the maximum overall. And neither has anything to do with the Title. Please try being a little more consistent.
I'll take description as what is actually wanted. For that use the Window function RANK within a sub select and the outer select just take rank 1.

select fname, sale, saledate
  from ( select cs.*, rank() over (partition by fname order by fname, sale desc) rk
           from cookiesales cs
       ) csr          
 where rk = 1;

Sorting date from a table

1 Answers1