I want to do a Select Distinct in my query but I only want it to apply to 3 columns. I want to return data from four columns though.
I have four columns that I want to return in my Select statement: Firstname, Lastname, PN, RefNum
I want to do a Select Distinct of only Firstname, Lastname, PN
If you have two rows that have the same FirstName, LastName, and PN, but have different RefNums, I think you want to get only one row back. And you do want to get all four columns. Given that there are two choices for the RefNum column, which of those do you want to get?
If you don't care which RefNum you get, you can query like this:
SELECT FirstName, LastName, PN,RefNum
FROM
(
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY FirstName, LastName, PN
ORDER BY (SELECT NULL)) AS N
FROM
YourTable
)s
WHERE N = 1
If you want to pick the highest value of RefNum, change (SELECT NULL) to RefNum DESC. You can apply a different order by clause if you have different rule to pick the RefNum.