SQL Query to Select Latest Version for Different Column Combinations

Question

I have a table like this:

Product	ImageVersion	ImageName	ImageBinary
Gizmo	1	Green	0x45679ABC
Gizmo	1	Blue	0x5679ABCD
Gizmo	1	Red	0x679ABCDE
Widget	1	Green	0x01234567
Widget	1	Blue	0x12345678
Widget	1	Red	0x23456789
Widget	2	Red	0x345679AB

I would like to get the latest ImageBinary for a given Product and ImageName

I have a query that is close. A, B, and C return the desired ImageBinary. D returns nothing. Can someone tell me what I am doing wrong?

-- A
SELECT a.ImageBinary
FROM [Database].[dbo].[DataTable] a
LEFT JOIN [Database].[dbo].[DataTable] b 
ON a.ImageName = b.ImageName AND a.ImageVersion < b.ImageVersion 
WHERE b.ImageVersion Is NULL AND a.ImageName = 'Green' AND a.Product = 'Widget'

-- B
SELECT a.ImageBinary
FROM [Database].[dbo].[DataTable] a
LEFT JOIN [Database].[dbo].[DataTable] b 
ON a.ImageName = b.ImageName AND a.ImageVersion < b.ImageVersion 
WHERE b.ImageVersion Is NULL AND a.ImageName = 'Green' AND a.Product = 'Gizmo'

-- C
SELECT a.ImageBinary
FROM [Database].[dbo].[DataTable] a
LEFT JOIN [Database].[dbo].[DataTable] b 
ON a.ImageName = b.ImageName AND a.ImageVersion < b.ImageVersion 
WHERE b.ImageVersion Is NULL AND a.ImageName = 'Red' AND a.Product = 'Widget'

-- D
SELECT a.ImageBinary
FROM [Database].[dbo].[DataTable] a
LEFT JOIN [Database].[dbo].[DataTable] b 
ON a.ImageName = b.ImageName AND a.ImageVersion < b.ImageVersion 
WHERE b.ImageVersion Is NULL AND a.ImageName = 'Red' AND a.Product = 'Gizmo'

===================================

USE [Database]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DataTable](
    [Product] [varchar](20) NOT NULL,
    [ImageVersion] [int] NOT NULL,
    [ImageName] [varchar](30) NOT NULL,
    [ImageBinary] [varchar](max) NULL,
 CONSTRAINT [PK_DataTable_1] PRIMARY KEY CLUSTERED 
(
    [Product] ASC,
    [ImageVersion] ASC,
    [ImageName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I realized I need to use a table with on before the join, then discovered queries can be joined. This works: — CodeRed2550000, Jun 09 '22 at 22:04
SELECT Q1.ImageBinary FROM ( SELECT t.Product, t.ImageVersion, t.ImageName, t.ImageBinary FROM [Database].[dbo].[DataTable] t WHERE t.Product = '' ) Q1 LEFT JOIN ( SELECT t.Product, t.ImageVersion, t.ImageName, t.ImageBinary FROM [Database].[dbo].[DataTable] t WHERE t.Product = '' ) Q2 ON Q1.ImageName = Q2.ImageName AND Q1.ImageVersion < Q2.ImageVersion WHERE Q2.ImageVersion Is NULL AND Q1.ImageName = '' AND Q1.Product = '' — CodeRed2550000, Jun 09 '22 at 22:04

The Impaler · Answer 1 · 2022-06-09T21:05:07.267

0

You can use ROW_NUMBER() to identify the rows you want. Then filtering out the other ones is easy. For example:

select *
from (
  select t.*,
    row_number() over(partition by product order by imagename desc) as rn
  from [Database].[dbo].[DataTable] t
) x
where rn = 1

edited Jun 09 '22 at 21:05

answered Jun 09 '22 at 19:12

The Impaler

45,731
9
39
76

I won't know the row number ahead of time. The selection needs to be the highest numbered ImageVersion for a given Product and ImageName. – CodeRed2550000 Jun 09 '22 at 20:29
@CodeRed2550000 It's sorted descending so you just take `rn = 1` always – Charlieface Jun 09 '22 at 20:49
@TheImpaler I think it should be `partition by product, imagename order by...` – Charlieface Jun 09 '22 at 20:50
@CodeRed2550000 `ROW_NUMBER()` is a function that produces serial numbers starting from 1 according to each grouping and ordering. Then, it's a matter of taking all the 1s. – The Impaler Jun 09 '22 at 21:05

SQL Query to Select Latest Version for Different Column Combinations

1 Answers1