Reputation: 35
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
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
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