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

Question

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?

Please, they are called columns, not fields.

ypercubeᵀᴹ
– ypercubeᵀᴹ

2014-03-27 20:05:31 +00:00
Commented Mar 27, 2014 at 20:05 — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Mar 27, 2014 at 20:05

Yazooney · Accepted Answer · 2014-03-27 19:30:19Z

2

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.

answered Mar 27, 2014 at 19:30

Yazooney

701 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Brad_Z Over a year ago

Correct answer. If you have an identity column in the table use that as your OrderBy to get the "first" row in the result set.

skyline01 Over a year ago

I like the added control with this solution. Unfortunately, as I begin to investigate my columns some more, it turns out there are other fields that behave like Field 3. (I have about 100 fields in all in the table.) So, I will have to identify all of them and exclude them from the partition.

skyline01 Over a year ago

Essentially, I am trying to emulate the "Remove Duplicates" feature in Excel, where all I would have to do is activate the checkbox for Field 1.

thepirat000 · Accepted Answer · 2014-03-27 19:13:43Z

1

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]

answered Mar 27, 2014 at 19:13

thepirat000

13.3k4 gold badges51 silver badges79 bronze badges

Comments

animateme · Accepted Answer · 2014-03-27 19:17:24Z

1

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]

answered Mar 27, 2014 at 19:17

animateme

3091 silver badge7 bronze badges

Comments

Taryn · Accepted Answer · 2014-03-27 19:24:00Z

0

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

edited Mar 27, 2014 at 19:24

Taryn

249k57 gold badges375 silver badges409 bronze badges

answered Mar 27, 2014 at 19:12

Cristian

1019 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related