CODEWITHSUNDEEP
sqlsql-server
user2125409
user2125409

Reputation: 3

Trying to Create a query that returns the oldest date for all records

I have an SQL Server Database and I have two tables, Certificates and Categories. There is a one to many relationship, each Certificate can have many categories. I'm trying to create a query that will show all of the certificates in the database, but only return one record with the oldest exp date for each category. I've looked up a post that does almost exactly what I'm looking for

But there is a finite number of records it is searching through. I've read around and found different ways to do this in other types of databases, but no efficient way to do this in ms sql server.

Upvotes: 0

Views: 110

Answers (3)

Muhammad Hani
Muhammad Hani

Reputation: 8664

The below query will fit the need. 

SELECT *
    FROM dbo.Certificates Cert INNER JOIN
    (SELECT CerificateId,MIN(ExpiryDate)
    FROM dbo.Categories
    GROUP BY CerificateId) AS LatestCategories
    ON Cert.CerificateId = LatestCategories.CerificateId

Upvotes: 0

dotNET
dotNET

Reputation: 35400

There is one efficient way to solve such queries in most databases that I know of, called the JOIN. Together with PK-FK relations and indexes, this would be efficient enough for most of the purpose you'd ever come across. Try learning these and you'll discover what I'm talking about.

For this specific problem, you simply need to JOIN the two tables and GROUP on the category field, using MIN aggregate function on exp date field. If you could supply the tables structure, we may be able to write the query too.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

For this, you want to use the row_number() function:

select c.*
from (select c.*,
             row_number() over (partition by category order by expdate desc) as seqnum
      from certificates c
     ) c
where seqnum = 1

This works on SQL Server versions 2005 and greater.

On older versions or in Access, you need to do a join to get this:

select c.*
from certifications c join
     (select category, max(expdate) as maxexpdate
      from certifications 
      group by category
     ) csum
     on c.category = csum.category and
        c.expddate = csum.maxexpdate

Upvotes: 2

Related Questions