Conditional grouping

• yevgeny 84062

  Grasshopper

  Points: 20

  More actions

  October 26, 2009 at 8:03 am

  #211454

  I am trying to get the first row of a grouped query without cursors. The cursor solution but that's taking forever.

  Say I have table T with columns, ID, Title, Price

  select * from T order by Price

  1title1850

  21title1950

  34title21100

  3title11200

  12title21300

  31title11430

  ...

  I need a query to only pick first rows of every group when grouped by Title and sorted by price. So, the query would return ID and Title.

  1title1

  34title2

  Help!

  Yevgeny

• mister boom

  Say Hey Kid

  Points: 663

  More actions

  October 26, 2009 at 8:10 am

  #1070712

  Hi

  Try this:

  SELECT * FROM

  (SELECT * ,ROW_NUMBER()

  OVER (PARTITION BY title Order BY price) as row_no

  FROM T) x

  WHERE ROW_NO = 1

  Regards

  Alun

• yevgeny 84062

  Grasshopper

  Points: 20

  More actions

  October 26, 2009 at 8:31 am

  #1070727

  Thanks, PARTITION BY is a great trick. I should start using it.