CODEWITHSUNDEEP
sql-serverrow
user4912958
user4912958

Reputation:

Row number by 3 distincts fields in SQL Server

I have this table:

| RecordLocator | DepartureStation | ArrivalStation | JourneyNumber | SegmentNumber | LegNumber | FlightNumber |
|---------------|------------------|----------------|---------------|---------------|-----------|--------------|
|    DADABC     |      MAO         |       GRU      |        2      |       1       |      1    |     1421     |
|    CEDLDA     |      MAO         |       STM      |        1      |       2       |      1    |     1643     |
|    CEDLDA     |      GRU         |       MAO      |        1      |       1       |      1    |     1640     |
|    DADABC     |      GRU         |       FLN      |        1      |       1       |      1    |     1848     |
|    CEDLDA     |      BEL         |       SLZ      |        1      |       2       |      3    |     1643     |
|    DADABC     |      GIG         |       FOR      |        1      |       2       |      3    |     1154     |
|    CEDLDA     |      SLZ         |       FOR      |        1      |       2       |      4    |     1680     |
|    CEDLDA     |      FOR         |       REC      |        1      |       2       |      5    |     1680     |
|    DADABC     |      FOR         |       MAO      |        1      |       2       |      4    |     1982     |
|    CEDLDA     |      REC         |       SSA      |        1      |       2       |      6    |     1680     |
|    CEDLDA     |      STM         |       BEL      |        1      |       2       |      2    |     1643     |
|    DADABC     |      POA         |       GIG      |        1      |       2       |      2    |     1201     |
|    CEDLDA     |      SSA         |       GRU      |        1      |       3       |      1    |     1817     |
|    DADABC     |      FLN         |       POA      |        1      |       2       |      1    |     1201     |

I want add a new column row number based on JourneyNumber, SegmentNumber and LegNumber, order by RecordLocator, for obtain this result:

| RecordLocator | DepartureStation | ArrivalStation | JourneyNumber | SegmentNumber | LegNumber | FlightNumber | rowNum |
|---------------|------------------|----------------|---------------|---------------|-----------|--------------|--------|
|    CEDLDA     |        GRU       |        MAO     |        1      |        1      |     1     |      1640    |   1    |
|    CEDLDA     |        MAO       |        STM     |        1      |        2      |     1     |      1643    |   2    |
|    CEDLDA     |        STM       |        BEL     |        1      |        2      |     2     |      1643    |   3    |
|    CEDLDA     |        BEL       |        SLZ     |        1      |        2      |     3     |      1643    |   4    |
|    CEDLDA     |        SLZ       |        FOR     |        1      |        2      |     4     |      1680    |   5    |
|    CEDLDA     |        FOR       |        REC     |        1      |        2      |     5     |      1680    |   6    |
|    CEDLDA     |        REC       |        SSA     |        1      |        2      |     6     |      1680    |   7    |
|    CEDLDA     |        SSA       |        GRU     |        1      |        3      |     1     |      1817    |   8    |
|    DADABC     |        GRU       |        FLN     |        1      |        1      |     1     |      1848    |   1    |
|    DADABC     |        FLN       |        POA     |        1      |        2      |     1     |      1201    |   2    |
|    DADABC     |        POA       |        GIG     |        1      |        2      |     2     |      1201    |   3    |
|    DADABC     |        GIG       |        FOR     |        1      |        2      |     3     |      1154    |   4    |
|    DADABC     |        FOR       |        MAO     |        1      |        2      |     4     |      1982    |   5    |
|    DADABC     |        MAO       |        GRU     |        2      |        1      |     1     |      1421    |   6    |

I tried this query:

SELECT  
    RecordLocator, 
    DepartureStation, ArrivalStation, 
    JourneyNumber, SegmentNumber, 
    LegNumber, FlightNumber, 
    (SELECT ((P.JourneyNumber + P.SegmentNumber + P.LegNumber)) 
     FROM PAX P2 
     WHERE P2.RecordLocator = P.RecordLocator 
       AND P2.DepartureStation = P.DepartureStation 
       AND P2.ArrivalStation = P.ArrivalStation 
       AND P2.JourneyNumber = P.JourneyNumber 
       AND P2.SegmentNumber = P.SegmentNumber
       AND P2.LegNumber = P.LegNumber
       AND P2.FlightNumber = P.FlightNumber) AS rowNum
FROM 
    PAX P

I'm trying sum the columns JourneyNumber, SegmentNumber and LegNumber but does not work, i belive best way to do, is based on Recordlocator define a "weight" for the columns JourneyNumber > SegmentNumber > LegNumber, but i don't know how implement it.

In C# I know how to do it, using align for:

// First `for` - Journey
for(int i = 0; i < Journey.Count(); i++)
{
    // Second `for` - Segment
    for(int j = 0; j < Segment.Count(); j++)
    {
        // Third `for` - Leg
        for(int k = 0; k < Leg.Count(); k++)
        {
            result = i + j + k;
        }
    }
}

Upvotes: 0

Views: 72

Answers (1)

John Bustos
John Bustos

Reputation: 19574

Basically, breaking down your question into its parts, you want to re-start numbering for each RecordLocator, then you order it by the 3 fields you wanted in ascending order.

Overall, that's the idea of the ROW_NUMBER() which allows for ordering your records with a partition set for RecordLocator to re-start the numbering as needed. So, something like this should do it:

ROW_NUMBER() OVER(PARTITION BY RecordLocator ORDER BY JourneyNumber, SegmentNumber, LegNumber)

And your final SQL, therefore, becoming:

SELECT  RecordLocator
      , DepartureStation
      , ArrivalStation
      , JourneyNumber
      , SegmentNumber
      , LegNumber
      , FlightNumber
      , ROW_NUMBER() OVER(PARTITION BY RecordLocator ORDER BY JourneyNumber, SegmentNumber, LegNumber) AS rowNum
FROM PAX P

Hope this does the trick!

Upvotes: 1

Related Questions