How do I tell the query to only use distinct on only one field

Question

I'm attempting to pull a report from this table:

CREATE TABLE [Miscellaneous].[BackupStatus](
    [BackupStatusId] [int] IDENTITY(1,1) NOT NULL,
    [CompanyCode] [varchar](3) NOT NULL,
    [ComputerName] [varchar](50) NOT NULL,
    [DateTimeOfBackup] [datetime] NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [IsSuccess] [bit] NOT NULL,
    [Message] [nvarchar](max) NOT NULL,
    [Exception] [nvarchar](max) NULL,
    [ProgramDate] [datetime] NULL,
 CONSTRAINT [PK_BackupStatus] PRIMARY KEY CLUSTERED 
(
    [BackupStatusId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [Miscellaneous].[BackupStatus] ADD  CONSTRAINT [DF_BackupStatus_IsSuccess]  DEFAULT ((0)) FOR [IsSuccess]
GO

I need to get a list of most recent backup by CompanyCode. I only want the most recent record for each company (ie CompanyCode). I believe that I need to sort the table decending and distinct but I cannot figure it out.

SELECT Distinct CompanyCode, ComputerName, DateTimeOfBackup, 
                [Description], IsSuccess, [Message], 
                [Exception], ProgramDate, BackupStatusId
FROM Miscellaneous.BackupStatus
ORDER BY DateTimeOfBackup DESC

I'm getting back all records still. How do I tell the query to use distinct on only one field?

UPDATE

I hope to add some clarity to what I need.

In this case, it should only return records 4262 and 4266, since they are the most recent unique for that CompanyCode.

Please show some sample data and desired results. It seems likely you want to use `row_number()` and filter only row number 1. — Dale K, Mar 04 '22 at 02:43
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Mar 04 '22 at 02:50
@dalek I really feel like you are attacking me. I would appreciate you didn't help me further. — ErocM, Mar 04 '22 at 04:13

T N · Accepted Answer · 2022-03-04T02:54:47.650

2

DISTINCT won't help here. You can use a windowed ROW_NUMBER() function to assign ordinals and then filter by ordinal = 1.

Something like:

SELECT CompanyCode, ComputerName, DateTimeOfBackup, 
       [Description], IsSuccess, [Message], 
       [Exception], ProgramDate, BackupStatusId
FROM (
    SELECT
        *,
        ordinal = ROW_NUMBER() OVER(PARTITION BY CompanyCode ORDER BY DateTimeOfBackup DESC)
    FROM Miscellaneous.BackupStatus
) A
WHERE A.Ordinal = 1 -- Just the latest
ORDER BY CompanyCode

This can also be coded using a CTE (common table expression):

WITH A AS (
    SELECT
        *,
        ordinal = ROW_NUMBER() OVER(PARTITION BY CompanyCode ORDER BY DateTimeOfBackup DESC)
    FROM Miscellaneous.BackupStatus
)
SELECT CompanyCode, ComputerName, DateTimeOfBackup, 
       [Description], IsSuccess, [Message], 
       [Exception], ProgramDate, BackupStatusId
FROM A
WHERE A.Ordinal = 1 -- Just the latest
ORDER BY CompanyCode

edited Mar 04 '22 at 02:54

answered Mar 04 '22 at 02:52

T N

4,322
1
5
18

2

This is known duplicate (as I have linked to), you should always flag a duplicate rather than answering it. – Dale K Mar 04 '22 at 02:53
1

@DaleK - Is there a topic-specific FAQ list that points to community recommended standard answers, how-to articles, or similar? I could start building my own favorite references, but don't want to duplicate effort. Sometimes searches return a mix of good and bad matches or good answers buried in more complexity than needed for a simple question. – T N Mar 04 '22 at 03:21
No, you have to maintain your own, but you've been active here a while now so I'm sure you're started to see common patterns with the questions. So keep a list as you find them and then flag them as duplicates. – Dale K Mar 04 '22 at 03:22
Nonetheless @TN thank you very much, that was exactly what I needed. I understand now how to do it thanks to your help. – ErocM Mar 04 '22 at 03:28

How do I tell the query to only use distinct on only one field

1 Answers1