display only line which begin with 1 and followed by 2

Question

Post reply

display only line which begin with 1 and followed by 2

samirca007

SSC-Addicted

Points: 417
More actions
June 11, 2020 at 9:39 am

#3762224
Good morning all ,
I use the row_number function to retrieve my contracts
I would like to display only the lists of lines which begin with 1 and followed by 2
```
 select row_number() over(partition by  NumeroContrat order by NumeroContrat desc) as classement , * from Gestion_Stats.dbo.produit
```
thanks
- This topic was modified 4 years, 6 months ago by samirca007.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

removed - attachment didn't show when writing original, this option wont meet requirements

samirca007 SSC-Addicted Points: 417 More actions · Answer 2

no

i want to display only

1

2

1

2

This reply was modified 4 years, 6 months ago by samirca007.

Ant-Green SSC Guru Points: 113507 More actions · Answer 3

One option using groupby and having

select 
row_number() over(partition by  NumeroContrat order by NumeroContrat desc) as classement, 
*
from Gestion_Stats.dbo.produit
where 
numerocontrat in 
(
  select 
    numerocontrat 
  from gestion_stats.dbo.produit 
  group by numerocontrat 
  having count(numerocontrat)>1
)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

SELECT *
FROM (
SELECT 
classement = row_number() over(partition by NumeroContrat order by NumeroContrat desc), -- *
GroupSize = COUNT(*) OVER(PARTITION BY NumeroContrat),
* 
FROM Gestion_Stats.dbo.produit
) d
WHERE GroupSize > 1 AND classement < 3

-- * Note that rows are randomly ordered within each NumeroContrat partition

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden