Results 1 to 3 of 3

Thread: Query design

  1. #1
    Join Date
    Oct 2003

    Unanswered: Query design

    I have the following table in MS SQL

    Batch Number Name
    A 1 Name 1
    A 2 Name 2
    A 3 Name 3
    A 4 Name 4
    B 5 Name 5
    B 6 Name 6
    B 7 Name 7
    B 8 Name 8
    B 9 Name 9
    C 10 Name 10
    C 11 Name 11
    C 12 Name 12
    C 13 Name 13
    C 14 Name 14

    I would like to run a query that returns the first name and last name by batch. I would like to use the number field to sort. When I am using the max(name) and min(name) it uses alphabetical order rather so it does not correspond with the order in the number field. The query that I am using is as follows:

    select min(name) as 'first name', max(name) as 'last name'
    from table
    group by batch

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    this will get what you want --
    select Batch, Number, Name
      from yourtable X
     where Number = 
           ( select min(Number)
               from yourtable
              where Batch = X.Batch )
        or Number = 
           ( select max(Number)
               from yourtable
              where Batch = X.Batch )
    if the min and max numbers for a batch are the same, this will return only one row

    if you need the names side by side on the same row, that's a different query

    rudy | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    There is often more than one way to skin a cat.

    I use this variation of r937's example, because it makes only one nested subquery call and uses joins rather than a WHERE clause:

    select Batch, Number, Name
    from yourtable
    inner join
    (select Batch, min(Number) as MinNumber, max(Number) as MaxNumber from yourtable group by Batch) BatchExtremes
    on yourtable.Number = BatchExtremes.MinNumber or yourtable.Number = BatchExtremes.MaxNumber


Posting Permissions

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