sql statement query last data

Question

I start to learn how to write sql language but I got stuck with the problem below : Now I have a data in a table named 'data'

+------+----------------------+
| id  | date                  |
+------+----------------------+
|    1 | 2009-05-10 09:17:25  |
|    2 | 2010-04-09 09:17:25  |
|    3 | 2010-12-12 09:17:25  |
|    4 | 2011-01-11 09:17:25  |
|    5 | 2012-03-19 09:17:25  |
|    7 | 2012-05-20 09:17:25  |
|    8 | 2013-02-21 09:17:25  |
|    9 | 2013-02-02 09:17:25  |
+------+----------------------+

I want to write an sql statement to get 3 last year from sysdate so the result I want to get is

+------+
| date |
+------+
| 2011 |
| 2012 |
| 2013 |
+------+

Assume that we don't exactly knew how many different types and number of column, so can you kindly guide me how to deal with this problem?

Thanks in advance

Answer 1

Try following query:

SELECT DISTINCT TOP(3) YEAR(date) AS date
FROM YourTable
ORDER BY YEAR(date) DESC

Answer 2

You can try this query:

WITH years([year])
AS
(
SELECT   DISTINCT TOP 3 YEAR([date]) AS [date]
FROM     Table1
ORDER BY YEAR([date]) DESC
)
SELECT TOP 3 [year]
FROM years 
ORDER BY [year] ASC;

SQL FIDDLE DEMO

Answer 3

You could use top:

SELECT   DISTINCT TOP 3 YEAR([date]) AS [date]
FROM     my_table
ORDER BY YEAR([date]) DESC

Answer 4

You can use the following query.

WITH years([year])
AS
(
    SELECT DISTINCT YEAR([date]) AS [year]
    FROM my_table
        ORDER BY YEAR([date]) DESC
)
SELECT TOP 3 [year]
FROM years 
ORDER BY [year] ASC;