select rows based on lowest value

• Jeff-132889

  Ten Centuries

  Points: 1112

  More actions

  March 27, 2007 at 5:44 pm

  #176897

  Hi,

  The title for this doesn't really explain my problem well so here goes:

  I have a table that has id, sequence, and name: 

  id           sequence         name

  1            0                    Al

  1            1                    Albert

  1            2                    Mr Albert

  2            1                    Joe

  2            4                    Joseph

  2            5                    Mr Joseph

   

  I need to select the name field based on the lowest sequence number for each id. I was hoping to use something similar to top(sequence) but lowest and I can't seem to either find a function or get the sytax correct.

  I have sorted by id, sequence so that the first occurrence of an ID is the lowest.......

  Thanks, Jeff

   

   

• R Michael

  SSCarpal Tunnel

  Points: 4211

  More actions

  March 28, 2007 at 12:04 am

  #697321

  Something like this would probably do the trick.

  SELECT m.id, m.sequence, t.name
  FROM (
          SELECT ID, MIN(sequence) AS Sequence
          FROM Table 
          GROUP BY ID
       ) m
      INNER JOIN Table t
          ON m.id = t.id
              AND m.sequence = t.sequence
  

  SQL guy and Houston Magician

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  March 28, 2007 at 12:07 am

  #697322

  Or this SQL Server 2005 way

  SELECT

  ID,

  Sequence

  ,

  Name

  FROM

  (

  SELECT ID,

  Sequence

  ,

  Name,

  ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Sequence) AS RecID

  FROM Table

  ) AS x

  WHERE

  RecID = 1

  ---

  N 56°04'39.16"
  E 12°55'05.25"