I have the following table:
CREATE TABLE orders (
id INT PRIMARY KEY IDENTITY,
oDate DATE NOT NULL,
oName VARCHAR(32) NOT NULL,
oItem INT,
oQty INT
-- ...
);
INSERT INTO orders
VALUES
(1, '2016-01-01', 'A', 1, 2),
(2, '2016-01-01', 'A', 2, 1),
(3, '2016-01-01', 'B', 1, 3),
(4, '2016-01-02', 'B', 1, 2),
(5, '2016-01-02', 'C', 1, 2),
(6, '2016-01-03', 'B', 2, 1),
(7, '2016-01-03', 'B', 1, 4),
(8, '2016-01-04', 'A', 1, 3)
;
I want to get the most recent rows (of which there might be multiple) for each name. For the sample data, the results should be:
id | oDate | oName | oItem | oQty | ... |
---|---|---|---|---|---|
5 | 2016-01-02 | C | 1 | 2 | |
6 | 2016-01-03 | B | 2 | 1 | |
7 | 2016-01-03 | B | 1 | 4 | |
8 | 2016-01-04 | A | 1 | 3 |
The query might be something like:
SELECT oDate, oName, oItem, oQty, ...
FROM orders
WHERE oDate = ???
GROUP BY oName
ORDER BY oDate, id
Besides missing the expression (represented by ???
) to calculate the desired values for oDate
, this statement is invalid as it selects columns that are neither grouped nor aggregates.
Does anyone know how to do get this result?
The rank
window clause allows you to, well, rank rows according to some partitioning, and then you could just select the top ones:
SELECT oDate, oName, oItem, oQty, oRemarks
FROM (SELECT oDate, oName, oItem, oQty, oRemarks,
RANK() OVER (PARTITION BY oName ORDER BY oDate DESC) AS rk
FROM my_table) t
WHERE rk = 1