CODEWITHSUNDEEP
sqlsql-servert-sql
JavaLearner
JavaLearner

Reputation: 637

Finding by latest value of a column along with other predicates

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.

Upvotes: 1

Views: 56

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

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);

Upvotes: 1

Sergey
Sergey

Reputation: 5217

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

Upvotes: 0

Related Questions