Today's question (by Alessandro Mortola):

Rows estimation with OPENJSON

create table tjson ( id int primary key, j1 varchar(max), j2 varchar(max), j3 varchar(max)); insert into tjson (id, j1, j2, j3) values (1, '[{"c11":"value11A", "c12":"value12A"},{"c11":"value11B", "c12":"value12B"}]', '[{"c21":"value21A", "c22":"value22A"},{"c21":"value21B", "c22":"value22B"}]', '[{"c31":"value31A", "c32":"value32A"},{"c31":"value31B", "c32":"value32B"}]');

select id, c1.c11, c2.c21, c2.c22, c3.c31, c3.c32 from tjson cross apply openjson(j1) with( c11 varchar(50) '$.c11', c12 varchar(50) '$.c12') c1 cross apply openjson(j2) with( c21 varchar(50) '$.c21', c22 varchar(50) '$.c22') c2 cross apply openjson(j3) with( c31 varchar(50) '$.c31', c32 varchar(50) '$.c32') c3;

Think you know the answer? Click here, and find out if you are right.

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 ) 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; 

Answer: 8

Explanation: As with a previous question, this has 8 unique values. They are: 1, 2, 3, 4, 5, 6, 7, 8. The 1 and 2 values are duplicated. Ref: DENSE_RANK - https://learn.microsoft.com/en-us/sql/t-sql/functions/dense-rank-transact-sql?view=sql-server-ver16

Discuss this question and answer on the forums

Here's a few of the new posts today on the forums. To see more, visit the forums.