Professional Web Applications Themes

Grouping and Ordering - Microsoft SQL / MS SQL Server

Hello Group, I am having problems generating the following result set... Can it be done? Raw Data ======== DateTime varA varB --------------------- ---- ---- 2003-04-28 09:27:00 0 1 2003-04-28 09:28:00 0 1 2003-04-28 09:29:00 0 1 2003-04-28 09:30:00 1 1 2003-04-28 09:31:00 0 0 2003-04-28 09:32:00 0 0 2003-04-28 09:33:00 1 1 2003-04-28 09:34:00 1 1 2003-04-28 09:35:00 0 0 2003-04-28 09:36:00 0 0 2003-04-28 09:37:00 0 0 2003-04-28 09:38:00 0 0 2003-04-28 09:39:00 0 0 2003-04-28 09:40:00 0 1 Desired Results =============== MinDate MaxDate varA varB --------------------- --------------------- ---- ---- 2003-04-28 09:27:00 2003-04-28 09:27:00 0 1 2003-04-28 09:30:00 2003-04-28 ...

  1. #1

    Default Grouping and Ordering

    Hello Group,

    I am having problems generating the following result set...
    Can it be done?

    Raw Data
    ========

    DateTime varA varB
    --------------------- ---- ----
    2003-04-28 09:27:00 0 1
    2003-04-28 09:28:00 0 1
    2003-04-28 09:29:00 0 1
    2003-04-28 09:30:00 1 1
    2003-04-28 09:31:00 0 0
    2003-04-28 09:32:00 0 0
    2003-04-28 09:33:00 1 1
    2003-04-28 09:34:00 1 1
    2003-04-28 09:35:00 0 0
    2003-04-28 09:36:00 0 0
    2003-04-28 09:37:00 0 0
    2003-04-28 09:38:00 0 0
    2003-04-28 09:39:00 0 0
    2003-04-28 09:40:00 0 1

    Desired Results
    ===============

    MinDate MaxDate varA varB
    --------------------- --------------------- ---- ----
    2003-04-28 09:27:00 2003-04-28 09:27:00 0 1
    2003-04-28 09:30:00 2003-04-28 09:30:00 1 1
    2003-04-28 09:31:00 2003-04-28 09:32:00 0 0
    2003-04-28 09:33:00 2003-04-28 09:34:00 1 1
    2003-04-28 09:35:00 2003-04-28 09:39:00 0 0
    2003-04-28 09:40:00 2003-04-28 09:40:00 0 1

    Select Min(DateTime) as MinDate, Max(DateTime) as MaxDate, varA, varB
    From MyData
    Group by varA, varB
    Order by DateTime

    Doesn't work.

    Cheers
    Damian Guest

  2. #2

    Default Re: Grouping and Ordering

    If in first row in your desired result set is MaxDate = '2003-04-28 09:27:00' then try this.

    create table test
    ( dtm datetime primary key
    , varA int not null
    , varB int not null
    )

    insert into test (dtm, varA, varB)
    select '2003-04-28 09:27:00', 0, 1
    union
    select '2003-04-28 09:28:00', 0, 1
    union
    select '2003-04-28 09:29:00', 0, 1
    union
    select '2003-04-28 09:30:00', 1, 1
    union
    select '2003-04-28 09:31:00', 0, 0
    union
    select '2003-04-28 09:32:00', 0, 0
    union
    select '2003-04-28 09:33:00', 1, 1
    union
    select '2003-04-28 09:34:00', 1, 1
    union
    select '2003-04-28 09:35:00', 0, 0
    union
    select '2003-04-28 09:36:00', 0, 0
    union
    select '2003-04-28 09:37:00', 0, 0
    union
    select '2003-04-28 09:38:00', 0, 0
    union
    select '2003-04-28 09:39:00', 0, 0
    union
    select '2003-04-28 09:40:00', 0, 1


    select
    RBr = (select count(*) from test ct where ct.dtm <= t1.dtm
    and convert(char(1), ct.varA) + convert(char(1), ct.varB)
    <> convert(char(1), t1.varA)+ convert(char(1), t1.varB))
    , *
    into #temp
    from test t1

    select
    MinDate = min(dtm), MaxDate = max(dtm), varA, varB
    from #temp
    group by RBr, varA, varB


    drop table test
    drop table #temp

    --
    Dean Savovic
    www.teched.hr


    "Damian Jolly" <com.au> wrote in message news:google.com... 


    Dean Guest

Similar Threads

  1. Grouping and ordering
    By miken32 in forum MySQL
    Replies: 7
    Last Post: July 12th, 12:03 AM
  2. Help Ordering Records
    By DogBot in forum Coldfusion Database Access
    Replies: 4
    Last Post: June 9th, 02:07 PM
  3. table ordering
    By patbegg in forum Dreamweaver AppDev
    Replies: 0
    Last Post: April 25th, 12:17 AM
  4. Ordering of recordsets
    By Ken Schaefer in forum ASP Database
    Replies: 2
    Last Post: August 20th, 12:45 PM
  5. [PHP] File ordering
    By Lowell Allen in forum PHP Development
    Replies: 1
    Last Post: August 12th, 02:04 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139