sql select query

I've a table开发者_Go百科 in the following format

ID   Revision     DocType
1     R1            1
2     R2            1
3     R2            2
4     R3            1

where Revision can be duplicated but DocType is unique per Revision. How can I write an SQL query to select a single record by providing Revision as a parameter?

Since Revision can be duplicate, the only way you can get a lone record by passing in a Revision as parameter is by doing:

SELECT TOP 1 ID,Revision,DocType 
from Revision 
where Revision=@RevisionPassedIn

If you can provide DocType as well, then you could uniquely select the record you are looking for:

SELECT ID,Revision,DocType 
from Revision 
where Revision=@RevisonPasseIn AND Doctype=@DocTypePassedIn

You've just described why you can't really achieve what you want - there can be multiple rows with the same Revision value.

If you're happy with getting the first row with that value, you can do something like:

SELECT TOP 1 *
FROM Table
WHERE Revision = @Revision
ORDER BY DocType -- or whatever criteria you want