Get 'most recent' grouped record (with order by)

Question

I have a query like the below

SELECT 
      t1.Supplier,
      t2.Product,
FROM 
      t1
INNER JOIN 
      t2 ON t1.ProductCode = t2.ProductCode
GROUP BY 
      t1.Supplier, t2.Product

On table t1, there are also columns called 'Timestamp' and 'Price' - I want to get the most recent price, i.e. SELECT Price ORDER BY Timestamp DESC. Can I do this with any aggregate functions, or would it have to be a subquery?

Make it easy to assist you - show us some sample table data _and the expected result_ - all as formatted text (not images.) [mcve] — jarlh, Sep 07 '21 at 09:53
Does this answer your question? [Get the latest records per Group By SQL](https://stackoverflow.com/questions/35219261/get-the-latest-records-per-group-by-sql) — Dale K, Sep 07 '21 at 10:35
Do you need the result based on your group shown in the query ? @Chris — Gudwlk, Sep 07 '21 at 11:05

score 2 · Answer 1 · answered Sep 07 '21 at 10:08

2

One standard way of doing this is to use ROW_NUMBER() to create an additional column in the source data, allowing you to identify which row is "first" within each "partition".

WITH
  supplier_sorted AS
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY supplier, ProductCode
                           ORDER BY timestamp DESC
                      )
                        AS recency_id
  FROM
   supplier
)
SELECT
  s.Supplier,
  p.Product,
  COUNT(*)
FROM
  supplier_sorted  AS s
INNER JOIN
  product          AS p
    ON s.ProductCode = p.ProductCode
WHERE
  s.recency_id = 1
GROUP BY
  s.Supplier,
  p.Product

answered Sep 07 '21 at 10:08

MatBailie

83,401
18
103
137

Why are you using Count(*) here ? – Gudwlk Sep 07 '21 at 11:08
@Gudwlk - As an example of where to use an aggregate. The OP used `GROUP BY` rather than `DISTINCT` ***and*** ended the `SELECT` clause with a `,` implying they intended to put "something" there. – MatBailie Sep 07 '21 at 12:30

score 1 · Answer 2 · answered Sep 07 '21 at 10:38

1

You can use cross apply:

SELECT t2.*, t1.*
FROM t2 CROSS APPLY
     (SELECT TOP (1) t1.*
      FROM t1
      WHERE t1.ProductCode = t2.ProductCode
      ORDER BY t1.TimeStamp DESC
     ) t1;

So, GROUP BY is not necessary.

answered Sep 07 '21 at 10:38

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Sep 07 '21 at 11:27

Can use the row_number() over the partiton of ProductCode and Supplier to by using Timestamp Order by desc to get the latest record by based on the partition. Then you can use in the same query without aggregation to get the desired result. It is good to use Windows functions rather than Group by for these questions.

SELECT 
   A.Supplier
  ,A.Product
  ,A.Price
FROM
  (
  SELECT 
     t1.Supplier,
     t2.Product,
     T1.Price,
     ROW_NUMBER () OVER ( PARTITION BY t1.Supplier,t2.Product ORDER BY  T1.[Timestamp] DESC ) AS  row_num    
   FROM   t1
    INNER JOIN t2 
      ON t1.ProductCode = t2.ProductCode
    ) AS A WHERE A.row_num = 1

Tested using below added data.

 CREATE TABLE t1
  ( Supplier varchar(100)
   ,ProductCode int 
   , Price Decimal (10,2)
   , [TimeStamp] datetime
  )

CREATE TABLE t2
( 
 ProductCode int 
 ,Product varchar(100)
 )

  insert into t1 values ('A', 1, 100.00, GetDate())
  insert into t1 values ('A', 1, 80.00, GetDate())
  insert into t1 values ('b', 2, 190.00, GetDate())
  insert into t1 values ('b', 2, 500.00, GetDate())

  insert into t2 values (1, 'Pro1')
  insert into t2 values (2, 'Pro2')
  insert into t2 values (3, 'Pro3')

Get 'most recent' grouped record (with order by)

3 Answers3