How do I create a customary ranking of my result set in SQL?

Question

Say I have a result set that can contain one or more values, but I always only want one value in the result, and specific values are worth more than others.

How would I solve this in sql so I can use it in a sub-query?

Example (T-SQL specific, that's the accent I work with):

I have a table:

tChore(ChoreId int primary key, ChoreDescription nvarchar(15))

which is loaded with all types of chores. I also have another table:

tDayChores(
    DayId int primary key,
    ChoreId int foreign key references tChore(ChoreId)
)

Which is loaded with days and chores in a many-to-many relationship.

There is of course a third table containing the name of all week days.

Now, lets say I would like to get one, and only one, day to vacuum. I prefer to do it on wednesdays, but if that's not scheduled then I'd like to do it on thursdays, and in turn if that's not scheduled then it doesn't matter, any day is fine.

One way to limit the result set to one row would be to do this:

select top(1)
    DayId
from
    tDayChores DC
    inner join tChore C
        on C.ChoreId = DC.ChoreId
where
    C.ChoreDescription = 'vacuum'

However, if vacuum is scheduled for monday and wednesday, then I would get monday as a result, and I'd like to rank wednesday highest.

Answer 1

Here's a solution using a sub-query:

select top(1)
    t.DayId
from (
        select
            DayId,
            case
                when DayId = 3 then 1 -- DayId: 3 = Wednesday
                when DayId = 4 then 2 -- DayId: 4 = Thursday
                else 3
            end DayRank
        from
            tDayChores DC
            inner join tChore C
                on C.ChoreId = DC.ChoreId
        where
            C.ChoreDescription = 'vacuum'
    ) t
order by t.DayRank

The day rank makes sure that wednesday and thursday are sorted before any other day, and then you simply pick the top one.