How to perform a select in a single statement?

Question

I have a Sql Server 2005 table with following data:

idHearing is the primary key (identity), idCase is a foreign key that may duplicate. StartDate and StartTime specify the date and time of the event - both fields are of DateTime type (for whatever reason these are separate fields). All StartDate data has a time of 12:00:00. All StartTime data has a date of 1/1/1900. IsScheduled and IsOnCalendar are bit fields.

My challenge is to select the latest (in terms of Date/Time) hearing for each idCase. If the StartDate/StartTime are identical (as is in the rows 1 and 2), preference should be given to a row which has IsScheduled and/or IsCalendar on. If those are columns are same as well, then it does not matter which row gets returned.

To make this more complicated, I must do it all in single SELECT statement (because it must be in a view) and I have to return all the columns that you see below.

I've tried several ways, but my SQL-FU isn't strong. Any ideas?

Answer 1

Use:

CREATE VIEW vw_summary AS
WITH example AS (
    SELECT t.idcase,
           t.startdate,
           t.startime,
           t.isscheduled,
           t.isoncalendar,
           ROW_NUMBER() OVER (PARTITION BY t.idcase ORDER BY t.startdate DESC, 
                                                             t.starttime DESC, 
                                                             t.isscheduled DESC,
                                                             t.isoncalendar DESC) AS rank
      FROM TABLE t)
SELECT e.*
  FROM example e
 WHERE e.rank = 1

Check & see - might tweak the ORDER BY on the ROW_NUMBER...

Answer 2

Pretty much the same as omg ponies answer. Row_number is your friend. I'm not entirely sure the bit fields are treated as you want, but you get the idea. As ever, it's always best to be explicit with the fields you are selecting, but I'm lazy.

create table #table 
(
    idHearing int,
    idCase int,
    startDate datetime,
    starttime datetime,
    isscheduled bit,
    isoncalendar bit
);
insert into #table values(1,1,'8/2/2010','3:30:00 PM',1,1)
insert into #table values(2,1,'8/2/2010','3:30:00 PM',1,0)
insert into #table values(3,2,'8/3/2010','5:30:00 PM',1,1)
insert into #table values(4,2,'8/4/2010','9:30:00 PM',1,1)
insert into #table values(5,3,'8/2/2010','3:00:00 PM',1,1)

select * from
(
    select 
        row_number() 
            over 
            (partition by idcase order by 
                startdate desc,
                starttime desc,
                isscheduled desc,
                isoncalendar desc
            ) idCasePosition,
        * 
    from #table
) x
where idCasePosition=1

drop table #table