Get the latest records per Group By SQL
Solution 1
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
Solution 2
This is a generic query without using analytical function.
SELECT a.*
FROM table1 a
INNER JOIN
(SELECT max(odate) modate,
oname,
oItem
FROM table1
GROUP BY oName,
oItem
)
b ON a.oname=b.oname
AND a.oitem=b.oitem
AND a.odate=b.modate
Solution 3
I think you need a query like this:
SELECT *
FROM (SELECT *,
ROW_NUMBER() OVER (PARTITION BY oName ORDER BY oDate DESC) seq
FROM yourTable) t
WHERE (seq <= 2)
ORDER BY oDate;
Solution 4
Add a primary key
suppose id
field to the table and make it auto increment,. Then order by id
you will get it. It is the traditional way. By using your table you can only order by oDate
. But is is having same date multiple times, so it also won't solve your problem.
Haminteu
Live as if you were to die tomorrow. Learn as if you were to live forever.
Updated on June 04, 2022Comments
-
Haminteu almost 2 years
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 foroDate
, this statement is invalid as it selects columns that are neither grouped nor aggregates.Does anyone know how to do get this result?
-
Sami Kuhmonen about 8 yearsThe result is not "latest record for each name" since there are duplicate names.
-
androidGenX about 8 years@Haminteu add primary key buddy it will solve your problem and you can order by that.
-
Haminteu about 8 years@androidGenX, Hi. Just edit the table. I have identity column on my table. Cheers.
-
Stanislovas Kalašnikovas about 8 years@Haminteu check an answer.
-
Jakub Kania about 8 yearsPossible duplicate of How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?
-