CODEWITHSUNDEEP
sqlsql-server
Steven Sevic
Steven Sevic

Reputation: 35

SQL How to return one ID in a select distinct query with multiple IDs in a table while still returning all other IDs as well (not just the top row)

I am using Microsoft SQL server. The following query produces a table much like this:

idnumber | checkID | taskID | status
1001     | 9000    | 410    | Approved
1001     | 9001    | 410    | Approved
1001     | 9002    | 410    | Approved
1201     | 9100    | 430    | Approved
1201     | 9101    | 430    | Approved

...and what I need is the query to just return one of the idnumbers, regardless of the other data (so that only one of the idnumbers is "approved" but not the others).

The result I would like would be similar to:

idnumber | checkID | taskID | status
1001 | 9000 | 410 | Approved
1201 | 9100 | 430 | Approved

My query so far:

SELECT DISTINCT viewTable.idnumber
              , viewTable.checkID
              , viewTable.taskID
              , 'Approved' AS status
FROM    viewTable

INNER JOIN anotherTable
    ON viewTable.idnumber = anotherTable.idnumber

INNER JOIN someOtherTable
ON someOtherTable.idnumber = anotherTable.idnumber

WHERE anotherTable.CODE = 'DesiredCode'

More about this issue: I have a specific value I am looking for in anotherTable.CODE, and only want to "approve" that specific code. The query above approves anything with an idnumber returned.

Upvotes: 1

Views: 1871

Answers (2)

Greg Viers
Greg Viers

Reputation: 3523

You can use an aggregate function such as max or min to only be interested in unique values from one column:

SELECT viewTable.idnumber
              , MAX(viewTable.checkID) AS checkID
              , MAX(viewTable.taskID) AS taskID
              , 'Approved' AS status
FROM    viewTable

INNER JOIN anotherTable
    ON viewTable.idnumber = anotherTable.idnumber

INNER JOIN someOtherTable
ON someOtherTable.idnumber = anotherTable.idnumber

WHERE anotherTable.CODE = 'DesiredCode'
GROUP BY viewTable.idnumber

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133410

A simple way is just use TOP 1 for get the firts row

  SELECT TOP 1 DISTINCT viewTable.idnumber
                , viewTable.checkID
                , viewTable.taskID
                , 'Approved' AS status
  FROM    viewTable

  INNER JOIN anotherTable
      ON viewTable.idnumber = anotherTable.idnumber

  INNER JOIN someOtherTable
  ON someOtherTable.idnumber = anotherTable.idnumber

  WHERE anotherTable.CODE = 'DesiredCode'

eventually with a proper order by

Upvotes: 1

Related Questions