Reputation: 1527
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
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
Upvotes: 1
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
Upvotes: 2
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
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