Brian Kelley

Brian Kelley is an author, columnist, and Microsoft SQL Server MVP focusing primarily on SQL Server security. He is a contributing author for How to Cheat at Securing SQL Server 2005 (Syngress), Professional SQL Server 2008 Administration (Wrox), and Introduction to SQL Server (Texas Publishing). Brian currently serves as an infrastructure and security architect. He has also served as a senior Microsoft SQL Server DBA, database architect, developer, and incident response team lead.
  • Interests: Chess, Reading, Soccer (Football), Baseball, Animals, Theology

Blogs

T-SQL Tuesday #183: Improving Permission Management

By

This is my (late) answer to my own invitation for T-SQL Tuesday #183. I...

Enterprise Digital Exhaust

By

Digital exhaust, or data exhaust, is the information you generate as you interact digitally....

Monday Monitor Tips: VLF Alerts

By

A recent change made to Redgate Monitor to add a new alert for VLF...

Read the latest Blogs

Forums

bulk load fails to for SAS when using the ms ODBC Driver 18 for SQL Server

By Johan Bijnens

bulk load fails to for SAS when using the ms ODBC Driver 18 for...

Upgrading from SQL Server 2016 to 2022

By zoggling

When we upgraded from SQL Server 2008 R2 to 2016 (Enterprise Edition) many years...

Unique Count of IDs per 3 month period

By SparkOfOrigin

I have an Appointments table with the fields MemberID and DateOfConsultation. Each member may...

Visit the forum

Question of the Day

The Rank Window II

I have this table and data:

CREATE TABLE [dbo].[SalesTracking]
(
[SalesDate] [datetime] NULL,
[SalesPersonID] [int] NULL,
[CustomerID] [int] NOT NULL,
[PONumber] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[paid] [bit] NULL,
[total] int
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [SalesTrackingCDX] ON [dbo].[SalesTracking] ([SalesDate]) ON [PRIMARY]
GO
INSERT dbo.SalesTracking
  (SalesDate, SalesPersonID, CustomerID, PONumber, paid, total)
VALUES
  ('2024-03-15 10:45:55.067', 1, 1,'PO965' ,1, 100),
  ('2023-09-24 10:45:55.067', 1, 2,'PO627' ,1, 200),
  ('2022-07-02 10:45:55.067', 1, 3,'PO6'   ,1, 300),
  ('2022-11-03 10:45:55.067', 1, 4,'PO283' ,1, 400),
  ('2022-11-26 10:45:55.067', 1, 5,'PO735' ,1, 500),
  ('2023-04-28 10:45:55.067', 1, 6,'PO407' ,1, 600),
  ('2022-09-09 10:45:55.067', 1, 7,'PO484' ,1, 700),
  ('2024-03-13 10:45:55.067', 1, 8,'PO344' ,1, 700),
  ('2024-04-24 10:45:55.067', 1, 9,'PO254' ,1, 800),
  ('2022-06-19 10:45:55.067', 1, 10,'PO344',1, 800)
GO
When I run this query, how many unique values are returned for the SaleRank column?
SELECT
  st.SalesDate
, st.SalesPersonID
, st.total
, DENSE_RANK () OVER (PARTITION BY st.SalesPersonID
                ORDER BY st.total desc) AS SaleRank
FROM dbo.SalesTracking AS st;

See possible answers