February 12, 2025 at 12:00 am
Comments posted to this topic are about the item The Rank Window
February 12, 2025 at 7:46 am
Nothing, really, seeing as there is not [total] column in the table...
February 12, 2025 at 8:30 am
Er, the schema is in want of a column.
February 12, 2025 at 1:43 pm
After correcting the script:
USE TEST
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 [money] NULL
) 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
, RANK () OVER (PARTITION BY st.SalesPersonID
ORDER BY st.total desc) AS SaleRank
FROM dbo.SalesTracking AS st;
The results were: 10 records
SalesDate SalesPersonID total SaleRank
2022-06-19 10:45:55.067 1 800.00 1
2024-04-24 10:45:55.067 1 800.00 1
2022-09-09 10:45:55.067 1 700.00 3
2024-03-13 10:45:55.067 1 700.00 3
2023-04-28 10:45:55.067 1 600.00 5
2022-11-26 10:45:55.067 1 500.00 6
2022-11-03 10:45:55.067 1 400.00 7
2022-07-02 10:45:55.067 1 300.00 8
2023-09-24 10:45:55.067 1 200.00 9
2024-03-15 10:45:55.067 1 100.00 10
February 12, 2025 at 1:59 pm
I also tried it with:
total [bigint] NULL
Results are the same 10 records
To get the 8 records, the select statement should look like this:
SELECT
st.SalesPersonID
, st.total
, RANK () OVER (PARTITION BY st.SalesPersonID
ORDER BY st.total desc) AS SaleRank
FROM dbo.SalesTracking AS st
group by st.SalesPersonID, st.total;
To get 8 records, the st.SalesDate needs to be removed from the Select statement.
SalesPersonID total SaleRank
1 800 1
1 700 2
1 600 3
1 500 4
1 400 5
1 300 6
1 200 7
1 100 8
February 12, 2025 at 2:03 pm
The question was "How many unique values are returned?", not "how many rows are returned that contain unique values?". So, other than the minor quibble with the missing column from the table definition, there's nothing really wrong with it. There are 8 unique values returned for SaleRank, even if two of them are repeated.
February 12, 2025 at 3:52 pm
Schema corrected and points awarded back. Rune is correct, this is asking for unique values, of which there are 8. Two ties.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy