Andy Leonard

  • Interests: SQL Server, Integration Services, Managing Technical Teams, Faith, Family, Farming.

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

SSIS Foreach From Variable Enumerator Multiple Variables

By 47u2caryj

Currently I have a working script task which reads a directory to find filename(s)...

Different number of records returned from SSMS and C# ExecuteReader

By tim8w

I am using the EXACT same query in SQL Server Management Studio (2014) as...

Column Encryption/Exporting Windows Certificate

By EsquireDeveloper

So random question about Windows Certificates -- I administer and develop a SQL Server...

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