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 batchthis 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|||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

blindman