display only line which begin with 1 and followed by 2

  • 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

    Sans titre

    thanks

    • This topic was modified 3 years, 9 months ago by  samirca007.
  • removed - attachment didn't show when writing original, this option wont meet requirements

  • no

    i want to display only

    1

    2

    1

    2

    • This reply was modified 3 years, 9 months ago by  samirca007.
  • 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
    )
  • 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

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

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