CODEWITHSUNDEEP
sql-server
skyline01
skyline01

Reputation: 2101

How to SELECT DISTINCT on 1 key column when querying multiple columns

I am working in SQL Server 2008 R2. I have a table in which 1 "record" is actually several records due to one of its fields having different values. This table has a lot of fields itself. All of the fields for a given "record" will have the same value, except for the one field that has various values. I want to query all records and return all fields, but I want only 1 instance (the first one) of each "record". For example, my table looks like:

Field 1    Field 2       Field 3
value a    value x       value 1
value a    value x       value 2
value a    value x       value 3
value b    value y       value 20
value b    value y       value 21
value b    value y       value 22

I want my query to return something like:

Field 1    Field 2       Field 3
value a    value x       value 1
value b    value y       value 20

So, my Field 1 is my key. Normally, I would just do a SELECT DISTINCT. However, since I want all fields to return in the query, the DISTINCT will apply to all of the fields in the SELECT, which means that all records will return, rather than just 1 for each key. What is the best way of accomplishing this?

Upvotes: 1

Views: 3569

Answers (4)

Yazooney
Yazooney

Reputation: 70

SELECT t.field1,t.field2,t.field3 FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION by Field1 ORDER BY Field1) AS RowNumber
FROM mytable
) t
WHERE t.RowNumber = 1

This method only looks at the distinct aspect of the first column. Order is not guaranteed in SQL. You can set how you define your "First Row" with the "ORDER BY" section of the above code.

Upvotes: 2

Cristian
Cristian

Reputation: 101

You can use Group By over field 1 and field 2;

Upvotes: 0

animateme
animateme

Reputation: 309

You'll want to group by Field 1 & Field 2 and then take the min of Field 3.

select      [Field 1],
            [Field 2],
            MIN([Field 3]) as [Field 3]
from        [SomeTable]
group by    [Field 1], [Field 2]

Upvotes: 1

thepirat000
thepirat000

Reputation: 13124

Supposing you want the minimum value from Field 3:

SELECT [Field 1], [Field 2], MIN([Field 3])
FROM YourTable
GROUP BY [Field 1], [Field 2]

Upvotes: 1

Related Questions