Get the latest records per Group By SQL

Question

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 result is not "latest record for each name" since there are duplicate names. — Sami Kuhmonen, Feb 05 '16 at 08:21
@Haminteu add primary key buddy it will solve your problem and you can order by that. — androidGenX, Feb 05 '16 at 08:22
@androidGenX, Hi. Just edit the table. I have identity column on my table. Cheers. — Haminteu, Feb 05 '16 at 08:25
Possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) — Jakub Kania, Feb 05 '16 at 08:38

Mureinik · Accepted Answer · 2019-02-12T06:43:30.110

12

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

edited Feb 12 '19 at 06:43

answered Feb 05 '16 at 08:22

Mureinik

297,002
52
306
350

score 3 · Answer 2 · answered Feb 05 '16 at 08:30

This is a generic query without using analytical function.

SQLFiddle Demo

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

shA.t · Answer 3 · 2016-02-05T08:28:30.873

2

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;

edited Feb 05 '16 at 08:28

answered Feb 05 '16 at 08:20

shA.t

16,580
5
54
111

score 2 · Answer 4 · answered Feb 05 '16 at 08:26

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.

Stanislovas Kalašnikovas · Answer 5 · 2016-02-05T08:27:11.477

You have to use ROW_NUMBER in following:

select oDate, oName, oItem, oQty, oRemarks
from (
   select *, row_number() over(partition by oName, oItem order by oDate desc) rn  
   from #t
)x
where rn = 1
order by oDate

OUTPUT

oDate   oName   oItem   oQty    oRemarks    
2016-01-01  A   001     2                   
2016-01-01  A   002     1       test        
2016-01-02  C   001     2                   
2016-01-03  B   001     4                   
2016-01-03  B   002     1

Get the latest records per Group By SQL

5 Answers5

Linked

Related