Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Help with Query 9i

    Hi

    Can't remember if I've asked this before but I urgently need some help.

    I have a 3 column table which gets populated every 15 min. There are no PKs and there will be 5 rows each for the same time say 8am, 8:15am, 8:30am....for each of the 3 nodes A, B, C.

    How would I select the max for each combination of time and node.

    Code:
    DATETIME	NODE	STATS1
    		
    15/05/2012 09:00	NODEA	108
    15/05/2012 09:00	NODEA	123
    15/05/2012 09:00	NODEA	131
    15/05/2012 09:00	NODEA	147
    15/05/2012 09:00	NODEA	114
    15/05/2012 09:00	NODEB	168
    15/05/2012 09:00	NODEB	7
    15/05/2012 09:00	NODEB	56
    15/05/2012 09:00	NODEB	45
    15/05/2012 09:00	NODEB	32
    15/05/2012 09:00	NODEC	99
    15/05/2012 09:00	NODEC	154
    15/05/2012 09:00	NODEC	84
    15/05/2012 09:00	NODEC	106
    15/05/2012 09:00	NODEC	159
    15/05/2012 08:45	NODEA	46
    15/05/2012 08:45	NODEA	67
    15/05/2012 08:45	NODEA	73
    15/05/2012 08:45	NODEA	96
    15/05/2012 08:45	NODEA	142
    15/05/2012 08:45	NODEB	142
    15/05/2012 08:45	NODEB	126
    15/05/2012 08:45	NODEB	86
    15/05/2012 08:45	NODEB	119
    15/05/2012 08:45	NODEB	88
    15/05/2012 08:45	NODEC	64
    15/05/2012 08:45	NODEC	55
    15/05/2012 08:45	NODEC	51
    15/05/2012 08:45	NODEC	68
    15/05/2012 08:45	NODEC	87
    15/05/2012 08:30	NODEA	48
    15/05/2012 08:30	NODEA	24
    15/05/2012 08:30	NODEA	34
    15/05/2012 08:30	NODEA	29
    15/05/2012 08:30	NODEA	77
    15/05/2012 08:30	NODEB	119
    15/05/2012 08:30	NODEB	43
    15/05/2012 08:30	NODEB	65
    15/05/2012 08:30	NODEB	86
    15/05/2012 08:30	NODEB	23
    15/05/2012 08:30	NODEC	16
    15/05/2012 08:30	NODEC	112
    15/05/2012 08:30	NODEC	99
    15/05/2012 08:30	NODEC	113
    15/05/2012 08:30	NODEC	32
    Regards
    Shajju

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    Try this:
    Code:
    SELECT DATETIME, NODE, MAX(STATS1)
    FROM tablename
    GROUP BY DATETIME, NODE

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks and sorry for not explaining the requirement very clearly. I actually have 5 columns:

    DATETIME NODE COL1 COL2 COL3

    and would like to pick out the whole row based on the max value in COL1 for each datetime, node combination.

  4. #4
    Join Date
    Jan 2012
    Posts
    84
    Code:
    SELECT *
    FROM tablename, 
    ( 
       SELECT datetime, node, max( col1 ) maxcol1
       FROM tablename
       GROUP BY datetime, node
    ) alias
    WHERE tablename.datetime = alias.datetime
      AND tablename.node = alias.node
      AND tablename.col1 = alias.maxcol1
    ;

Posting Permissions

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