February 15, 2018 at 9:46 am
How can i get a distinct list of the lcc_codes below removing null lcc_codes and with a row number while keeping the same order of the original data?
This is a sample set of data with ID column and LCC_CODE column.
values
('1', 'IFRE')
,('2', 'IFRE')
,('3', 'IHMR')
,('4', NULL)
,('5', NULL)
,('6', NULL)
,('7', 'IHBR')
,('8', NULL)
,('9', NULL)
,('10', NULL)
) x(id, lcc_code)
Here is what i would like result to look like:
(id, lcc_code)
('1', 'IFRE')
,('2', 'IHMR')
,('3', 'IHBR')
February 15, 2018 at 9:57 am
Get the min ID, group by code and order by min ID.
February 15, 2018 at 10:11 am
Luis Cazares - Thursday, February 15, 2018 9:57 AMGet the min ID, group by code and order by min ID.
The min id put me on the right track. But that didn't give me the new row numbers i was looking for. I just had to do a row_number over order by min id to get the result i was looking for.
Here is the final query. Thanks.
select
x.lcc_code
,row_number() over (order by min(x.id)) rownum
from
(
values
('1', 'IFRE')
,('2', 'IFRE')
,('3', 'IHMR')
,('4', NULL)
,('5', NULL)
,('6', NULL)
,('7', 'IHBR')
,('8', NULL)
,('9', NULL)
,('10', NULL)
) x(id, lcc_code)
where x.lcc_code is not null
group by x.lcc_code
February 15, 2018 at 10:44 am
I completely missed the part where you wanted to reassign row numbers.
I'm glad you got it working.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply