Santosh Jadi
Santosh Jadi

Reputation: 1527

Serial number auto generation

Facing issue in auto generation of serial number.

How can i use row_number() in proper manner or is there any alternative to achieve the expected results?

Note: I want to write query without using CASE.

SELECT 
 row_number() over (order by COALESCE(legs.sta, legs.eta) ASC) sno,
 legs.Flight_ID flightId,
 legs.flight,
 legs.flightDate           
FROM Flt_OperativeFlight_Legs LEG
ORDER BY COALESCE(legs.sta, legs.eta) ASC

Current Results:

+------+--------+-------------------------+
| sno  | flight |       flightDate        |
+------+--------+-------------------------+
|    1 | 3K0722 | 2019-01-08 17:10:00.000 |
|    2 | 3K0722 | 2019-01-08 20:20:00.000 |
|    3 | 3K0723 | 2019-01-09 17:10:00.000 |
|    4 | 3K0724 | 2019-01-10 20:20:00.000 |
+------+--------+-------------------------+

Expected Results:

+------+--------+-------------------------+
| sno  | flight |       flightDate        |
+------+--------+-------------------------+
|    1 | 3K0722 | 2019-01-08 17:10:00.000 |
|      |        | 2019-01-08 20:20:00.000 |
|    2 | 3K0723 | 2019-01-09 17:10:00.000 |
|    3 | 3K0724 | 2019-01-10 20:20:00.000 |
+------+--------+-------------------------+

Upvotes: 1

Views: 141

Answers (4)

Eralper
Eralper

Reputation: 6612

Please refer to following solution, to empty repeating data I required to use CASE expression

Within CTE expression I used Row_Number and Dense_Rank functions in combination with Partition By clause

;with cte as (
    select
        ROW_NUMBER() over (order by flight, flightDate) as num,
        DENSE_RANK() over (order by flight) as [no],
        ROW_NUMBER() over (partition by flight order by flightDate) as rn,
        flight,
        flightDate
    from Flt_OperativeFlight_Legs legs
)
select
    case when rn > 1 then null else [no] end as [no],
    case when rn > 1 then null else flight end as flight,
    flightDate
from cte
order by num

Output is as follows

enter image description here

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to use ROW_NUMBER window function in a subquery, then use DENSE_RANK window function to create no column with CASE WHEN expression get rn = 1 row.

SELECT (CASE WHEN rn = 1 THEN DENSE_RANK() over(order by flight) END) no,
       (CASE WHEN rn = 1 THEN flt_id END) flt_id,
       (CASE WHEN rn = 1 THEN flight END) flight,
       flightDate
FROM 
(
    SELECT *,ROW_NUMBER() OVER(PARTITION BY flt_id,flight ORDER BY flightDate) rn
    FROM Flt_OperativeFlight_Legs 
) t1

sqlfiddle

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need other row_number() :

SELECT DENSE_RANK() OVER (ORDER BY flt_id, flight) AS Sr,
       (CASE WHEN Seq = 1 
             THEN flightId
        END) AS Flight_ID,
       (CASE WHEN Seq = 1 
             THEN flight
        END) AS flight, flightDate
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Flight_ID, flight ORDER BY flightDate) AS Seq,
             legs.Flight_ID flightId, legs.flight, legs.flightDate
      FROM Flt_OperativeFlight_Legs LEG
     ) l;

Upvotes: 2

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use DENSE_RANK instead of ROW_NUMBER

SELECT 
 DENSE_RANK() over (order by COALESCE(legs.sta, legs.eta, legs.ata) ASC) as "s.no",
 legs.Flight_ID flightId
 ,legs.flight
 ,legs.flightDate
FROM Flt_OperativeFlight_Legs LEG
ORDER BY COALESCE(legs.sta, legs.eta, legs.ata) ASC

Upvotes: 1

Related Questions