CODEWITHSUNDEEP
sqlsql-server
sylvanaar
sylvanaar

Reputation: 8216

Taking only 1 row from a group of rows sharing the same field value

I have a SQL Server 2008 database with records like the following

001 CAT 1 2 3 
002 DOG 3 3 1
003 DOG 2 1 1
004 DOG 2 1 3
005 CAT 1 3 4

I want to take 1 row for each unique value in column 2 (cat and dog)

so this would get me (one possible answer):

001 CAT 1 2 3
002 DOG 3 3 1

Column 1 is the PK and is a string. Column 2 is a string

Columns 3-5 are just there to indicate there are other variable fields which I need in the final result.

Upvotes: 1

Views: 107

Answers (2)

AdaTheDev
AdaTheDev

Reputation: 147374

SELECT t1.* 
FROM YourTable t1
    JOIN 
    (SELECT MIN(Col1) AS FirstId 
     FROM YourTable GROUP BY Col2) x ON t1.Col1 = x.FirstId

Upvotes: 5

brettkelly
brettkelly

Reputation: 28245

SELECT pk, DISTINCT pet, col3, col4, col5 FROM myPetsTable

Would that work? Taking a shot in the dark here :)

Upvotes: 0

Related Questions