SQL query to select all records from the last 5 years

I have a table called recruits that has columns including first, last, and class. The first and last columns are collectively the primary keys and represent a recruit's first and last names, respectively. The class column is the year in which the recruit has committed. Note that there can be multiple recruits in the same class, ie have the same class value.

I want to display all records from the most recent class--whatever it might be--as well as the ones from the four previous classes/years. I don't really want to hard-code a range of specific years because I would have 开发者_如何学Pythonmanually change it whenever a new class/year comes along.

Any help is appreciated. Let me know if my description is too vague.

EDIT: I'm just using MySQL/Apache friends

---

Using first+last as the PK means that a person cannot be recruited more than once. It also prevents a second John Doe from recruiting when another has already recruited in a previous year.

select r.*
from
(
    # The 5 most recent classes
    select class
    from recruits
    group by class
    order by class desc
    limit 5
) c
inner join recruits r on r.class = c.class
order by class desc, `first`, `last`

I take it you mean last 5 years present in the data rather than last 5 calendar years from current.

---

Assuming your storing the year as 2006, or 2011 and your using TSQL

select 
    First, Last, Class
From
    recruits
where
    class > datepart(yy,getdate())-5

This will get you anyone who joined post 2006

---

Since you didn't provide a specific SQL server, you can try this (should work on many RDBMS):

select *
  from recruits where class in
     (select distinct class from recruits order by recruits desc limit 2)

---

sounds like you neeed something like:

Select
    *
From
    recruits
Where 
    class between DatePart(yy, getdate()) and DatePart(yy, getdate()) - 4

Might be able to get a little better answer depending on what exactly your using for your db.

This is transact sql from sql server.

---

In most databases, something like

SELECT first, last, class
  FROM (SELECT first, 
               last, 
               class, 
               dense_rank() over (order by class) rank_of_class
          FROM recruits)
 WHERE rank_of_class <= 5

will return all the information for the last 5 class values.