SQL Select Distinct with ints and varchars

Question

I have a query which returns a number of ints and varchars.

I want the result Distinct but by only one of the ints.

SELECT DISTINCT
        t.TaskID ,
        td.[TestSteps]
FROM    SOX_Task t
snip

Is there a simple way to do this?

Answer 1

DISTINCT doesn't work that way ... it ensures that entire rows are not duplicated. Besides, if it did, how would it decide which values should appear in other columns?

What you probably want to do here is a GROUP BY on the column you want to be distinct, and then apply an appropriate aggregate operator to the other columns to get the values you want (eg. MIN, MAX, SUM, AVG, etc).

For example, the following returns a distinct list of task IDs with the maximum number of steps for that task. That may not be exactly what you want, but it should give you the general idea.

SELECT t.TaskID, MAX(t.TestSteps)
FROM SOX_Task t
GROUP BY t.TaskID

Answer 2

I want the result Distinct but by only one of the ints.

This:

SELECT DISTINCT t.taskid
  FROM SOX_TASK t

...will return a list of unique taskid values - there will not be any duplicates.

If you want to return a specific value, you need to specify it in the WHERE clause:

SELECT t.*
  FROM SOX_TASK t
 WHERE t.taskid = ?

That will return all the rows with the specific taskid value. If you want a distict list of the values associated with the taskid value:

SELECT DISTINCT t.*
  FROM SOX_TASK t
 WHERE t.taskid = ?

GROUP BY is another means of getting distinct/unique values, and it's my preference to use, but in order to use GROUP BY we need to know the columns in the table and what grouping you want.