help with filter: query

[LikeThisName]

i have a query that returns the names of people with a window office, most of these people are part of a Group A. A few of these window offices are shared by two people, but I only want to return one name per room.

I can return one name already incorporating vbscript to filter the array i build from the sql query, but for learning purposes would like to know how to do all of the following all on SQL Server.

i would like to return only one name per office number, if there are two names associated to the room i only want to return the name that is part of the highest group)

SELECT     person.name, person.office_room_number, MapCoordsNames.class, MapCoords.group
FROM         dbo.MapCoordsNames INNER JOIN
                      dbo.person ON person.office_room_number = MapCoordsNames.office_room_number  INNER JOIN
                      dbo.MapCoords ON MapCoords.office_room_number  
                      = MapCoordsNames.office_room_number 
WHERE     (person.Active_Employee = 1)
ORDER BY MapCoordsNames.class, person.office_room_number, MapCoords.group

LikeThisName <- ?

 

[swampBoogie]

SELECT max(person.name) as name, 
       person.office_room_number, 
       MapCoordsNames.class, 
       MapCoords.group
FROM   dbo.MapCoordsNames 
 INNER JOIN dbo.person ON person.office_room_number = MapCoordsNames.office_room_number  
 INNER JOIN dbo.MapCoords ON MapCoords.office_room_number = MapCoordsNames.office_room_number
 WHERE person.Active_Employee = 1
 group by person.office_room_number, 
       MapCoordsNames.class, 
       MapCoords.group
ORDER BY MapCoordsNames.class, 
         person.office_room_number, 
         MapCoords.group

 

[LikeThisName]

i can't seem to get the above to work.

i simplified my query a little bit

name is actually an expression concatonated field as well as office room_number on the joins

this was the actual query, and i can't group this as is.

SELECT     TOP 100 PERCENT dbo.person.first_name + ' ' + dbo.person.last_name AS Name, dbo.person.office_room_number AS located, 
                      dbo.MapCoordsNames.class, dbo.MapCoords.x, dbo.MapCoords.y, dbo.person.attorney
FROM         dbo.MapCoordsNames INNER JOIN
                      dbo.person ON dbo.person.office_room_number = CAST(dbo.MapCoordsNames.Floor AS varchar(2)) 
                      + CAST(dbo.MapCoordsNames.Number AS varchar(5)) INNER JOIN
                      dbo.MapCoords ON CAST(dbo.MapCoordsNames.Floor AS varchar(2)) + CAST(dbo.MapCoordsNames.Number AS varchar(5)) 
                      = CAST(dbo.MapCoords.Floor AS varchar(2)) + CAST(dbo.MapCoords.Number AS varchar(5))
WHERE     (dbo.person.Active_Employee = 1) AND (dbo.MapCoordsNames.Floor = '10')
ORDER BY dbo.MapCoordsNames.class, dbo.person.office_room_number, dbo.person.attorney

any ideas? what exactly would your example do? does max only return the highest number


LikeThisName <- ?

 

[swampBoogie]

SELECT   max(dbo.person.first_name + ' ' + dbo.person.last_name) AS Name, 
      dbo.person.office_room_number AS located,
      dbo.MapCoordsNames.class, 
      dbo.MapCoords.x, dbo.MapCoords.y, 
      dbo.person.attorney
FROM  dbo.MapCoordsNames INNER JOIN
      dbo.person ON dbo.person.office_room_number = CAST(dbo.MapCoordsNames.Floor AS varchar(2))
              + CAST(dbo.MapCoordsNames.Number AS varchar(5)) INNER JOIN
      dbo.MapCoords ON CAST(dbo.MapCoordsNames.Floor AS varchar(2)) + CAST(dbo.MapCoordsNames.Number AS varchar(5))
              = CAST(dbo.MapCoords.Floor AS varchar(2)) + CAST(dbo.MapCoords.Number AS varchar(5))
WHERE (dbo.person.Active_Employee = 1) AND (dbo.MapCoordsNames.Floor = '10')
group by dbo.person.office_room_number,
      dbo.MapCoordsNames.class, 
      dbo.MapCoords.x, dbo.MapCoords.y, 
      dbo.person.attorney
ORDER BY dbo.MapCoordsNames.class, dbo.person.office_room_number, dbo.person.attorney