Click here to Skip to main content
15,040,743 members
Home / Discussions / Database
   

Database

 
QuestionConvert or use expression in Sql select statement Pin
indian14323-Aug-17 13:49
Memberindian14323-Aug-17 13:49 
AnswerRe: Convert or use expression in Sql select statement Pin
David Mujica24-Aug-17 4:03
MemberDavid Mujica24-Aug-17 4:03 
AnswerRe: Convert or use expression in Sql select statement Pin
ZurdoDev24-Aug-17 4:50
professionalZurdoDev24-Aug-17 4:50 
QuestionSSIS Package creating XML Pin
Member 1335791511-Aug-17 19:59
MemberMember 1335791511-Aug-17 19:59 
AnswerRe: SSIS Package creating XML Pin
jschell14-Aug-17 8:28
Memberjschell14-Aug-17 8:28 
Questionsql Query help Pin
venu6568-Aug-17 23:53
Membervenu6568-Aug-17 23:53 
AnswerRe: sql Query help Pin
Richard MacCutchan9-Aug-17 0:19
mveRichard MacCutchan9-Aug-17 0:19 
AnswerRe: sql Query help Pin
Richard Deeming9-Aug-17 1:19
mveRichard Deeming9-Aug-17 1:19 
Assuming you're using Microsoft SQL Server, something like this should work:
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 (Transact-SQL) | Microsoft Docs[^]

NB: You should avoid using spaces or special characters in table and column names. It makes it harder to query the data correctly. Instead, change the display names in your application's UI.



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer


GeneralRe: sql Query help Pin
venu6569-Aug-17 17:54
Membervenu6569-Aug-17 17:54 
GeneralRe: sql Query help Pin
Richard Deeming10-Aug-17 0:49
mveRichard Deeming10-Aug-17 0:49 
QuestionSQL boolean comparison Pin
User 1106097926-Jul-17 21:25
MemberUser 1106097926-Jul-17 21:25 
AnswerRe: SQL boolean comparison Pin
Richard MacCutchan26-Jul-17 22:03
mveRichard MacCutchan26-Jul-17 22:03 
GeneralRe: SQL boolean comparison Pin
User 1106097926-Jul-17 22:40
MemberUser 1106097926-Jul-17 22:40 
GeneralRe: SQL boolean comparison Pin
Richard MacCutchan26-Jul-17 22:48
mveRichard MacCutchan26-Jul-17 22:48 
JokeRe: SQL boolean comparison Pin
User 1106097926-Jul-17 23:14
MemberUser 1106097926-Jul-17 23:14 
GeneralRe: SQL boolean comparison Pin
User 1106097927-Jul-17 22:40
MemberUser 1106097927-Jul-17 22:40 
GeneralRe: SQL boolean comparison Pin
Richard MacCutchan27-Jul-17 22:59
mveRichard MacCutchan27-Jul-17 22:59 
GeneralRe: SQL boolean comparison Pin
Member 133967936-Sep-17 14:04
MemberMember 133967936-Sep-17 14:04 
AnswerRe: SQL boolean comparison Pin
Mycroft Holmes26-Jul-17 22:23
professionalMycroft Holmes26-Jul-17 22:23 
GeneralRe: SQL boolean comparison Pin
User 1106097926-Jul-17 22:42
MemberUser 1106097926-Jul-17 22:42 
AnswerRe: SQL boolean comparison Pin
Richard Deeming27-Jul-17 1:41
mveRichard Deeming27-Jul-17 1:41 
GeneralRe: SQL boolean comparison Pin
User 1106097927-Jul-17 1:53
MemberUser 1106097927-Jul-17 1:53 
GeneralRe: SQL boolean comparison Pin
User 1106097927-Jul-17 22:41
MemberUser 1106097927-Jul-17 22:41 
QuestionSQL Linq, getting 3 of each record. Pin
jkirkerx24-Jul-17 10:16
professionaljkirkerx24-Jul-17 10:16 
AnswerRe: SQL Linq, getting 3 of each record, [WAIT] Pin
jkirkerx24-Jul-17 10:29
professionaljkirkerx24-Jul-17 10:29 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.