CHACO CHACO - 1 month ago 7
SQL Question

#SQL - Order By matching record first

i need help to order this table (named "season") , by matching actual date with the BEGINDATE

ID NAME BEGINDATE
----------- -------------------- ----------
1 2014-2015 2014-10-01
2 2015-2016 2015-10-01
3 2016-2017 2016-10-01
4 2017-2018 2017-10-01


for example:
actual date is 2016/10/28 so we are in season 2016-2017 (id=3)
so the result should be

ID NAME BEGINDATE
----------- -------------------- ----------
3 2016-2017 2016-10-01
1 2014-2015 2014-10-01
2 2015-2016 2015-10-01
4 2017-2018 2017-10-01


UPDATE (SOLVED)

what i finally did was:

DECLARE @IDACTIVE AS INT = (SELECT MAX(ID) FROM SEASON WHERE BEGINDATE < GETDATE())

SELECT
1 AS ORDERBY,
ID,
NAME,
BEGINDATE
FROM SEASON
WHERE ID = @IDACTIVE

UNION

SELECT
2 AS ORDERBY,
ID,
NAME,
BEGINDATE
FROM SEASON
WHERE ID = @IDACTIVE

Answer

Follow the next approach:

1) Get The only matched row by using Top and Where clauses.

2) Get the all records except the one that you getting on point #1

3) Combine the result of two Selects via using UNION ALL.

Demo:-

Create table season (id int , NAME varchar(20),BEGINDATE date)

go
insert into season values (1,'2014-2015','2014-10-01')
insert into season values (2,'2015-2016','2015-10-01')
insert into season values (3,'2016-2017','2016-10-01')
insert into season values (4,'2017-2018','2017-10-01')

go

select  * from (
    select top 1 * from season
    where BEGINDATE < getdate()
    order by BEGINDATE desc
) a

union all

select * from season 
where BEGINDATE != (
            select top 1 BEGINDATE from season
            where BEGINDATE < getdate()
            order by BEGINDATE desc)

-- an another Soluation 

select * from season
where DATEPART(Year,BEGINDATE) =DATEPART(Year,getdate())

union all

select * from season 
where DATEPART(Year,BEGINDATE) !=DATEPART(Year,getdate())

The Result:

enter image description here