rownum & group by question

My query

select key1,
       key2,
       key3,


       -- Current year

       case rownum when 1 then  dateVal end date1,
       case rownum when 1 then  amtVal  end amt1,
       case rownum when 1 then  amtVal  end amt2,


       -- Previous Year

       case rownum when 2 then  dateVal end date2,
       case rownum when 2 then  amtVal  end amt2,
       case rownum when 2 then  amtVal  end amt2,
       --- Previous Previous year ;)
       case rownum when 3 then  dateVal end date3,
       case rownum when 3 then  amtVal  end amt3,
       case rownum when 3 then  amtVal  end amt3,

from
       db.myTable
where
       rownum < 3
order by
      dateVal desc

Hi all

I am stumped. This query returns the first 3 rows in order. No problem.

  Year1/data
  Year2/data
  Year3/data

What would be really handy though, would be to use the "group by" so I only get one row back.

Problem is, if I use a "group by", I have to put the "rownum" into my "group by" clause and this would defeat the purpose.

Maybe I am missing something obvious? Received on Tue Mar 21 2006 - 14:47:17 CST