Finding by latest value of a column along with other predicates

Question

I have a schema as following:

Products(name, company_id, version)

Adding some dummy data:

name	company_id	version
a	1	1
b	1	1
a	1	2
c	2	1
a	2	1

I want to find all the rows which have name=a, company_id=1 and the version is latest.

I looked at Fetch the row which has the Max value for a column and build a query as following

SELECT * FROM Products p
WHERE p.name = a AND p.company_id = 2 AND p.version =
    (SELECT MAX(q.version) FROM Products q 
    WHERE q.name = a AND q.company_id = 2)

Is there any better way in which these type of queries can be designed? I'm using SQL Server 2014.

Tim Biegeleisen · Accepted Answer · 2021-08-12T07:41:47.967

1

Using TOP, we can try:

SELECT TOP 1 *
FROM Products
WHERE name = 'a' AND company_id = 1
ORDER BY version DESC;

A generalization to get the most recent version for every company in the table:

SELECT TOP 1 WITH TIES *
FROM Products
WHERE name = 'a'
ORDER BY ROW_NUMBER() OVER (PARTITION BY company ORDER BY version DESC);

edited Aug 12 '21 at 07:41

answered Aug 12 '21 at 07:10

Tim Biegeleisen

502,043
27
286
360

Thanks for the answer. Can this be generalized to return all the latest products filtered by `company_id`? – JavaLearner Aug 12 '21 at 07:40
1

@JavaLearner Yes, you can use a `TOP 1 WITH TIES` trick, with the help of `ROW_NUMBER`, q.v. my updated answer. – Tim Biegeleisen Aug 12 '21 at 07:42
It took me a while to understand the query but now that I understand it, it's beautiful. – JavaLearner Aug 12 '21 at 08:47

score 0 · Answer 2 · edited Aug 12 '21 at 07:40

0

SELECT TOP 1 P.*
FROM PRODUCTS AS P
WHERE P.name = 'A' AND P.company_id = 2
ORDER BY P.version DESC

I guess, you can try this

edited Aug 12 '21 at 07:40

Dale K

25,246
15
42
71

answered Aug 12 '21 at 07:10

Sergey

4,719
1
6
11

Finding by latest value of a column along with other predicates

2 Answers2