SQL SORT BY ALTERNATE YEAR

3
votes

Existing Data: 

RowId   Title   Year
1        Aaa    2002
2        Bbb    2003
3        Ccc    2004
4        Ddd    2004
5        Eee    2005
6        Fff    2006
7        Ggg    2007
8        Hhh    2008

I have a need to retrieve the data from the above table based on the input parameter, year as follows:

    1.  Select all records matching param year
    2.  Select all records matching param year +1
    3.  Select all records matching param year -1
    4.  Select all records matching param year +2
    5.  Select all records matching param year -2
    6.  Select all records matching param year +3
    7.  Select all records matching param year -3

Expected Results (assuming param year is 2005):

RowId   Title   Year
5        Eee    2005
6        Fff    2006
3        Ccc    2004
4        Ddd    2004
7        Ggg    2007
2        Bbb    2003
8        Hhh    2008
1        Aaa    2002

I was able to achieve it using multiple SELECT on same table with UNION but wondering if there is a better way to do it.

Appreciate your help!

4
sqlsql-serversorting

4 Answers

3
votes

Assuming Year is an integer:

declare @param int

select *
from mytable
where Year = @param
or Year = @param + 1
or Year = @param - 1
-- etc.
order by abs(Year - @param), Year - @param desc, Title

The first clause in the order by sorts by the magnitude of the difference between Year and @param, and the second makes sure positive differences come before negative ones.

1
votes
SELECT * FROM TableName
WHERE Year = @Year Or 
      Year = @Year - 1 Or 
      Year = @Year + 1 Or
      Year = @Year - 2 Or 
      Year = @Year + 2 Or
      Year = @Year - 3 Or 
      Year = @Year + 3 
ORDER BY ABS(Year - @Year)
0
votes

I would do it like this:

select [Row], [Title], [Year], abs(year-2005) as thing
from table1
order by thing, year desc

fiddle: http://sqlfiddle.com/#!6/6e5e4/2

0
votes

you would need to change 2005 for a param but this should work 

select rowid, title, year FROM
(
select rowid, title, year, (year - 2005) c1, ABS (year - 2005) c2  from a
  ) t1 
  order by t1.c2, t1.c1 desc

http://sqlfiddle.com/#!6/b42d0/4