How to select first distinct rows sequentially?

Transact-SQL
1

Hello everyone, I am stuck here because the problem seems easy but I can't find the proper way to do it.

A sub-query gets me this data:

d s
2021-7 0
2021-8 0
2021-9 0
2021-10 0
2021-11 0
2021-12 470
2022-1 770
2022-2 470
2022-3 470
2022-4 470
2022-5 470
2022-6 470
2022-8 975
2022-9 975
2022-10 975
2022-11 975
2022-12 975
2023-1 975

I would like to retrieve only the first distinct values sequentially ie

d s
2021-7 0
2021-12 470
2022-1 770
2022-2 470
2022-8 975

Is this possible with a "native" SQL query?

Thank you for your guidance
Dave

2

hi hope this helps

create sample data script

create table #SampleData( d varchar(10) , s int )
insert into #SampleData select '2021-7',0
insert into #SampleData select '2021-8',0
insert into #SampleData select '2021-9',0
insert into #SampleData select '2021-10',0
insert into #SampleData select '2021-11',0
insert into #SampleData select '2021-12',470
insert into #SampleData select '2022-1',770
insert into #SampleData select '2022-2',470
insert into #SampleData select '2022-3',470
insert into #SampleData select '2022-4',470
insert into #SampleData select '2022-5',470
insert into #SampleData select '2022-6',470
insert into #SampleData select '2022-8',975
insert into #SampleData select '2022-9',975
insert into #SampleData select '2022-10',975
insert into #SampleData select '2022-11',975
insert into #SampleData select '2022-12',975
insert into #SampleData select '2023-1',975

;
 WITH CTE AS 
  (  SELECT
	      ROW_NUMBER() OVER(PARTITION BY S ORDER BY CAST(D+'-01'  AS DATE )) AS RN
	   ,  * 
	 FROM 
	    #SampleData)
 SELECT 
       * 
 FROM 
    CTE 
 WHERE 
     RN = 1

image
image417×544 6.5 KB

1 Like
3

Impressive! Thank you. I'll need to understand how and why this works now :smiley:

4

if you would like an explanation

please let me know

thank you

this is the part that does the trick
i convert to date
ORDER BY CAST(D+'-01' AS DATE )

5

I just ran the code with the example data... it only returns 4 rows instead of the 5 that were identified in the original post.

You'll need a "LAG" in there.

6

Using the "Readily Consumable" test data that @harishgg1 supplied, the following will return the expected "1" qualifier markings where the adjacent row has a change in the value of 's', along with the full dates created for the ORDER BY, both in the LAG and in the final query. This isn't the final query, We're just demonstrating what will be returned by the CTE.

 SELECT d,s
        ,ShowMe  = IIF(LAG(s,1,NULL) OVER (ORDER BY CONVERT(DATE,D+'-01')) = s,0,1)
        ,TheDate = CONVERT(DATE,D+'-01')
   FROM #SampleData
;

That returns the following where you can easily see when the value of "s" changes. There are, in fact, 5 such changes.

image
image552×674 10.9 KB

Then, use that in a CTE and complete the code the same way that @harishgg1 did...

   WITH CTE AS 
(--==== This earmarks where 's' changed from row-to-row in date order
 SELECT d,s
        ,ShowMe  = IIF(LAG(s,1,NULL) OVER (ORDER BY CONVERT(DATE,D+'-01')) = s,0,1)
        ,TheDate = CONVERT(DATE,D+'-01')
   FROM #SampleData
)--==== This selects only the earmarked rows in date order.
 SELECT d,s
   FROM CTE 
  WHERE ShowMe = 1
  ORDER BY TheDate
;

Here's the 5 row output from that.
image