SQL Trying to select only the first record out of several returned.

Question

I have a query that returns students and their roommates. The idea is to have everyone show up in the first column and then column 2 and 3 to have each roommate. Column 3 could be blank, but columns 1 and 2 will always have results.

My query is returning the correct data with the exception of when I there are 3 people in the room, I will get more than one record. such as below.

_roommate1  roommate2 roommate3  room1_   
_roommate1  roommate3 roommate2  room1_  
_roommate2  roommate1 roommate3  room1_  
_roommate2  roommate3 roommate1  room1_   
_roommate3  roommate1 roommate2  room1_  
_roommate3  roommate2 roommate1  room1_  

All I want to do is keep the first instance of each row when the field is appears in column 1.

so I would like to reduce the output to:

_roommate1  roommate2 roommate3  room1_  
_roommate2  roommate1 roommate3  room1_  
_roommate3  roommate1 roommate2  room1_  

The intent is to send a form letter to each person in column 1 to tell them the names of their roommates.

Since I can only use distinct on a row and not on a field, I am at a temporary loss as how I might be able to do this. What I would like to be able to do is say if it worked is:

SELECT (DISTINCT Column1), column2, column3 so i would only get the first instance of each column1. Any suggestions?

Answer 1

Sounds like a GROUP BY might be useful?

http://www.w3schools.com/sql/sql_groupby.asp

Answer 2

Try this

SELECT Column1, MAX(Column2) AS Column2, MAX(Column3) AS Column3
FROM MyTable
GROUP BY Column1

Answer 3

It's a crazy question. You're doing something wrong. Why do you need to execute this query? Break this up into multiple queries and aggregate the data in your programming. It seems you're asking the database to do something non-standard and strange.