T-sql 2012 priortize values in a column

In a t-sql 2012, I have a table that looks like the following:

CREATE TABLE [dbo].[Enroll](
[enrollID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[calendardate] [int] NOT NULL,
[serviceType] varchar NOT NULL,
[active] [bit] NOT NULLNOT NULL),
[startDate] [smalldatetime] NOT NULL,
[endDate] [smalldatetime] NULL,
[endYear] [smallint] NOT NULL
)

The table is not really looked at by enrollID. This table usually accessed by personID, service type, startdate and enddate.
For each personID there can be lots of records.

For the most current record (selected by start date and end date), I want to look for records
where the service Type= P. If there is no service type=P, then I want to pick a record if service type=N.

Thus would you show me the t-sql 2012 for a way on how to accomplish this goal?

Maybe this:

select * /* specify which fields you want to see */
  from (select * /* specify which fields you want to see */
              ,row_number() over(partition by personid
                                 order by case when servicetype='P' then 0 else 1 end
                                         ,enddate desc
                                         ,startdate desc
                                )
               as rn
          from dbo.enroll
         where servicetype in ('P','N')
       ) as a
 where id=1
;
where rn=1 ?
1 Like

Right :slight_smile:

1 Like

How about if the data is sorted by service type in descending order? This way P would sort be for N.

Change the

order by case when servicetype='P' then 0 else 1 end

to force the sort order you want.

1 Like

You listed the above code "order by case when servicetype='P' then 0 else 1 end".

I have the following questions:

  1. Is your sql better than just sorrint by an descending value? Can you tell me why it is better? is that due to performance?
  2. The logic ' then 0 else 1 end ', how I am suppose to check for the values of 0 or 1? Is there a field I should look at and what is the field and how to I check for the value? If not, is this logic just part of the sql logic that is used?

The CASE was so that the ServiceType "P" would be sorted first (Case = 0) and anything else would be sorted second (Case= 1), then the results would be sorted by EndDate DESC and then StartDate DESC

You can just sort by descending ServiceType, that will sequence "P" before "N" of course, and that would be fine.

It depends a bit what the requirements are. If the requirements were "WHRE ServiceType is in a user-defined-list of options but ALWAYS sort "P" first, then using CASE would be the answer.

If the query is hardwired to be only "P" and "N" then sort descending on ServiceType is of course fine.

Only other option is if the code is subject to maintenance in future and whether the "side effect" of descending sort on ServiceType (i.e. bringing "P" before "N") would be realised, whereas clearly the CASE code explicitly shows what the intention is. A COMMENT on the Descending Sort would achieve the same objective :slight_smile:

Probably none of those things matter, so just mentioning them "in case they are important".

I doubt there is much difference in performance, but if ServiceType is indexed it is possible that SQL would be able to use that index for the ORDER BY (assuming that the index also covers the other parts of the query)

I wouldn't worry about the performance of the Sort Order method too much - the other aspects of the query, in particular any JOINs and the WHERE clause, are much more likely to influence the performance, but if performance is critical (or slower than expected/acceptable) then I would test the various options and see how their performance compares.

1 Like