Database

With Numbering
As
(Select *,(case when len(phone) > 0 then 1
           else 0
		   end) as Number
From    dbo.table2)
select classid,count(student_name),sum(number)
from Numbering
group by classid;

SELECT CASE WHEN MCTR_DESCR IN ('Large', 'Midsize') THEN 'LG' ELSE '' END ...

Member 13357915 wrote:

It's lists all odd numbered records first then all the even numbered ones.

Member 13357915 wrote:

so I cannot see why this is happening.

WITH cte As
(
    SELECT
        ID,
        [Device id],
        TimeZone,
        [Effective Date],
        ROW_NUMBER() OVER 
        (
            PARTITION BY 
                [Device id] 
            ORDER BY
                CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
                [Effective Date] DESC
        ) As RN
    FROM
        Timetable
)
SELECT
    ID,
    [Device id],
    TimeZone,
    [Effective Date]
FROM
    cte
WHERE
    RN = 1
;

ROW_NUMBER() OVER 
(
    PARTITION BY 
        [Device id] 
    ORDER BY
        CASE WHEN [Effective Date] <= @YourDateParameter THEN 0 ELSE 1 END,
        CASE WHEN [Effective Date] <= @YourDateParameter THEN [Effective Date] ELSE Null END DESC,
        CASE WHEN [Effective Date] <= @YourDateParameter THEN Null ELSE [Effective Date] END
) As RN

1. Dates on or before the date parameter come first;
2. Dates on or before the parameter are sorted in descending order, so the latest comes first;
3. Dates after the parameter are sorted in ascending order, so the earliest comes first;

bool b1= true;
bool b2= false;
bool res= b1 == b2;

(SQL != C++) && (SQL != C#)

(SQL != C++) && (SQL != C#)

a XOR b <==> (a AND (NOT b)) OR ((NOT a) AND b)

... WHERE ((TBL1.INTFIELD Is Null And TBL2.INTFIELD Is Not Null) Or (TBL1.INTFIELD Is Not Null And TBL2.INTFIELD Is Null))