SQL To SELECT MIN

• Express12

  SSCoach

  Points: 18514

  More actions

  August 9, 2005 at 1:14 pm

  #90306

  What would be the best way to UPDATE TBL_A -- setting OrderID to the MIN(OrderID) for COMMON ExamID's.

  TBL_A:

  OrderID ExamID

  1       234085

  2       234085

  3       234085

  4       234085

  5       333777

  6       333777

  7       333777

  NEED the RESULT To look like:

  TBL_A:

  OrderID ExamID

  1       234085

  1       234085

  1       234085

  1       234085

  5       333777

  5       333777

  5       333777

  BT

• Ray M

  SSC-Insane

  Points: 21093

  More actions

  August 9, 2005 at 1:43 pm

  #580844

  Something like this.

  Whether its the best way or not is up to debate

  create table TBL_A(OrderID int, ExamID int)

  insert into TBL_A(OrderID, ExamID)

  Select 1,       234085

  union

  Select 2       ,234085

  union

  Select 3       ,234085

  union

  Select 4       ,234085

  union

  Select 5       ,333777

  union

  Select 6       ,333777

  union

  Select 7       ,333777

   

  update A

  set OrderID = (select min(orderID) from TBL_A b where a.ExamID = b.ExamID)

  from TBL_A A

  select * from TBL_A

  Result

  OrderID ExamID

  1  234085

  1  234085

  1  234085

  1  234085

  5  333777

  5  333777

  5  333777

   

• Express12

  SSCoach

  Points: 18514

  More actions

  August 9, 2005 at 2:45 pm

  #580857

  thanks!  that worked great

  BT