How to select unique values looking at only one column in SQL Server?

Question

I use the following query (in SQL Server 2008) to fetch the last 20 records from a table.

The query works as intended, my only problem is that this is a log table where a certain itemID can appear several times. Is there a way that I can only select unique itemIDs here?

I tried using DISTINCT but this doesn't work here as not the whole rows are duplicates, just the itemID can appear multiple times (and then the time stamps or other data differ).

My SQL:

ALTER PROCEDURE [dbo].[RC_FetchUpdates]
    @departmentID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      TOP 20 A.itemID,
                A.lastUpdate,
                CONVERT(VARCHAR(11), A.modTime, 106) AS modTime,
                A.modBy,
                B.itemName,
                B.linkRef
    FROM        RC_LogLinks A
    LEFT JOIN   RC_Links B
    ON          B.itemID = A.itemID
    WHERE       B.departmentID = @departmentID
    AND         A.lastUpdate <> 'Deleted'
    ORDER BY    A.modTime desc, B.itemName
    FOR XML PATH('updates'), ELEMENTS, TYPE, ROOT('ranks')

END

Use ROW_NUMBER OVER (PARTITION BY itemID ORDER BY someother col) as rn.. WHERE rn=1 — Mihai, Oct 06 '14 at 21:56
this very much depends on which instance of each itemID you want to see... — Daniel E., Oct 06 '14 at 21:57
@DanielE.: Thanks - I only want to see the latest instance, i.e. the one with the newest modTime. — user2571510, Oct 06 '14 at 22:02
Try @Mihai answer with `Order By ModTime desc` then. note that the whole query needs to be wrapped in an outer select to use `where rn=1` — Daniel E., Oct 06 '14 at 22:04
Thanks - sorry, I am not sure I understand how to implement this as I am still pretty new to SQL. — user2571510, Oct 06 '14 at 22:07
no time today, but look at http://stackoverflow.com/questions/17259716/selecting-specific-row-number-in-sql for the basic idea, the accepted answer shows what i mean. — Daniel E., Oct 07 '14 at 00:53

score 2 · Accepted Answer · answered Oct 07 '14 at 01:15

It will be something like this:

WITH rankedLogLinks AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY A.itemID ORDER BY A.modTime DESC) AS rownum, A.itemID, A.lastUpdate, A.modTime, A.modBy
    FROM RC_LogLinks A
    WHERE A.lastUpdate <> 'Deleted'
)
SELECT  r.itemID, r.lastUpdate, CONVERT(VARCHAR(11), r.modTime, 106) AS modTime, r.modBy, B.itemName, B.linkRef
FROM rankedLogLinks r
    LEFT JOIN   RC_Links B
       ON B.itemID = r.itemID
    WHERE B.departmentID = @departmentID
       AND r.rownum = 1
    ORDER BY B.itemName
    FOR XML PATH('updates'), ELEMENTS, TYPE, ROOT('ranks')
;

You might want to change the location of WHERE A.lastUpdate <> 'Deleted' from the first query to the second depending on your requirement.

This is perfect and helps a lot - thanks very much ! – user2571510 Oct 07 '14 at 06:01 — user2571510, Oct 07 '14 at 06:01

How to select unique values looking at only one column in SQL Server?

1 Answers1