Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Rogue River, Or
    Posts
    5

    Question Unanswered: Trying to return the first (or last, depending on the sort) record

    I have a SQL statement where I am linking a table back to itself and want to return all columns when a condition is met. The SQL looks like the following:

    select a.id id,
    max(a.event) event,
    max(a.event_datetime) event_start,
    max(b.event_DATETIME) event_end
    from events a, events b
    where trunc(a.event_datetime) = trunc(sysdate-1) and
    a.id = b.id and
    a.event_datetime < b.event_datetime and
    a.event <> 'W' and b.event in ('1','2')
    group by a.id, a.event_DATETIME

    The data looks like the following:

    id event event_datetime
    01 G 11:06:31
    01 B 11:10:01
    01 C 11:12:49
    01 F 12:15:44
    01 E 12:25:26
    01 2 12:31:47
    02 C 8:43:56
    02 F 9:12:26
    02 1 9:35:50

    The expected results are as follows:

    for id = 01 id=01,
    event=E,
    event_start=12:25:26,
    event_end=12:31:47
    for id = 02 id=02,
    event=F
    event_start=9:12:26
    event_end=9:35:50

    What I am getting is:

    for id = 01 id=01,
    event=G, <- should be E
    event_start=12:25:26,
    event_end=12:31:47
    for id = 02 id=02, <- correct
    event=F
    event_start=9:12:26
    event_end=9:35:50

    Max on the a.event will obviously not work as the events are not necessarily in order. event_datetime is in order and I just need to get the event that matches the event_datetime.

    Any help will be appreciated.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    Sorry I do not quite follow your question. I assume though that you are aware that ORDER BY is valid for a statement which includes GROUP BY and that there is a HAVING clause.

    select col1, col2, sum( col3 )
    from ....
    where .....
    group by col1, col2
    having sum( col3 ) > 3
    order by col2

    Hth
    Bill
    Last edited by billm; 12-18-03 at 00:29.
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Dec 2003
    Location
    Noida, India.
    Posts
    171

    Re: Trying to return the first (or last, depending on the sort) record

    I am trying to understand your requirement.
    Do you require

    col1= id,
    col2= max event for that id
    col3 = time for this event as event_start
    col4 = max time for this id as event_end

    Is my understanding is right?


    select id, max(event)

    Originally posted by jpuehl
    I have a SQL statement where I am linking a table back to itself and want to return all columns when a condition is met. The SQL looks like the following:

    select a.id id,
    max(a.event) event,
    max(a.event_datetime) event_start,
    max(b.event_DATETIME) event_end
    from events a, events b
    where trunc(a.event_datetime) = trunc(sysdate-1) and
    a.id = b.id and
    a.event_datetime < b.event_datetime and
    a.event <> 'W' and b.event in ('1','2')
    group by a.id, a.event_DATETIME

    The data looks like the following:

    id event event_datetime
    01 G 11:06:31
    01 B 11:10:01
    01 C 11:12:49
    01 F 12:15:44
    01 E 12:25:26
    01 2 12:31:47
    02 C 8:43:56
    02 F 9:12:26
    02 1 9:35:50

    The expected results are as follows:

    for id = 01 id=01,
    event=E,
    event_start=12:25:26,
    event_end=12:31:47
    for id = 02 id=02,
    event=F
    event_start=9:12:26
    event_end=9:35:50

    What I am getting is:

    for id = 01 id=01,
    event=G, <- should be E
    event_start=12:25:26,
    event_end=12:31:47
    for id = 02 id=02, <- correct
    event=F
    event_start=9:12:26
    event_end=9:35:50

    Max on the a.event will obviously not work as the events are not necessarily in order. event_datetime is in order and I just need to get the event that matches the event_datetime.

    Any help will be appreciated.
    Oracle can do wonders !

  4. #4
    Join Date
    Dec 2003
    Location
    Rogue River, Or
    Posts
    5
    Sorry, I'll try to word the problem better.

    Basically, what I am trying to do is find the begin and end time for the last event. With the example data below, event "1" is the final event for the object, or "id". I am trying build a record that contains the event prior to the last event, in this case "B", that contains it's time as the begin time, "11:12" and the time of the last event, "12:15" for event "1".

    id event time
    01 E 11:06
    01 H 11:10
    01 B 11:12
    01 1 12:15

    With the SQL in this post, I get the following

    id event begin end
    01 E 11:06 11:10
    01 H 11:10 11:12
    01 B 11:12 12:15 <---- this is the record I want to select

    I am trying to return the last record as indicated above. If I change the group by to "group by a.id", I get 1 record returned with the correct times but I have to "max", (or "min" or something) a.event and I don't get the correct value. In this example a "min" would return the correct value of "B", but the value to be returned will not always be the "min" value, or the "max" value, ie "B" could be "F".

    Thanks again for the help.

Posting Permissions

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