September 29, 2011 at 2:50 am
Does anyone have an idea how to tweak the TSQL above to exclude assigning a row_number to rows for a particular column that has NULL values?
This might not be the right approach, but any other approach would be appreciated.
I'm currently doing it by joining (LEFT) with the same table (without the null values) and assigning new row_numbers, but would love to achieve this using an inbuilt function or another approach.
Thanks.
Kwex.
September 29, 2011 at 2:58 am
What T-SQL above?
September 29, 2011 at 2:58 am
It's difficult to work out what you require from your description, but it sounds like you just need to add WHERE particular_column IS NOT NULL to the end of your query. If that doesn't fit, please provide table DDL, sample data and expected results, and show us what you've tried so far.
John
September 29, 2011 at 3:06 am
Or just a combination of case and Row_Number:
CASE WHEN Column IS NOT NULL THEN ROW_NUMBER() OVER (PARTITION BY Column ORDER BY blah) ELSE NULL END
But it's difficult to work out exactly what you want without any details...
September 29, 2011 at 3:06 am
Thanks guys.
The TSQL Phrase is
SELECT *, ROW_NUMBER() OVER (PARTITION BY colA ORDER BY colB) FROM tbl;
Adding more light to my initial post, I desire to return all rows in the given table, but to assign row_numbers to rows for a particular column (say colC) that has NON-NULL values.
As far as I'm aware, you cannot assign a WHERE clause to the ROW_NUMBER() function, thus, it would assign row numbers to all rows governed by the PARTITION clause only.
Kwex.
September 29, 2011 at 3:25 am
Did you try Howard's suggestion?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2011 at 3:37 am
GilaMonster (9/29/2011)
Did you try Howard's suggestion?
Yes I have. This just sets that row to NULL, and still uses up the numbering. Thus, the next NON-NULL row would not have [Prev NOT-NULL row] + 1.
E.g
colC RowNum
----- -------------
122 1
NULL NULL
444 3
What I want to achieve is this
colC RowNum
----- -------------
122 1
NULL NULL
444 2
555 3
NULL NULL
NULL NULL
666 4
Thanks.
September 29, 2011 at 3:48 am
You'll have to post some DDL and sample queries for us to help you further with this, otherwise we're wasting time guessing at what you want and getting it wrong.
As a general piece of advice, it sounds like you'll want to do something like do one query with a ROW_NUMBER() and a WHERE clause that excludes the rows where whichever column is NULL (it's obviously not your partition column by your results) and then UNION ALL it to another query with a hard coded NULL in the row number column and a where clause that only includes the NULL rows.
September 29, 2011 at 3:49 am
Try what I suggested and UNION it with the rows with NULL in ColC. Still waiting for the DDL and sample data so that we can visualise and test this.
John
September 29, 2011 at 4:30 am
Thanks guys for taking time to respond.
I'm not sure what level of DDL you expect, but I hope this can suffice.
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE #test
GO
CREATE TABLE #test (ID INT IDENTITY, colC INT ) ;
INSERT INTO #test VALUES ( 111 ) ;
INSERT INTO #test VALUES ( 222 ) ;
INSERT INTO #test VALUES ( NULL ) ;
INSERT INTO #test VALUES ( NULL ) ;
INSERT INTO #test VALUES ( 555 ) ;
INSERT INTO #test VALUES ( 666 ) ;
INSERT INTO #test VALUES ( NULL ) ;
INSERT INTO #test VALUES ( '777' ) ;
SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ID ) RowNum
FROM #test
This is what comes out
ID colC RowNum
----- ----- -------------
1 111 1
2 222 2
3 NULL 3
4 NULL 4
5 555 5
6 666 6
7 NULL 7
8 777 8
What I want to achieve is this
ID colC RowNum
----- ----- -------------
1 111 1
2 222 2
3 NULL NULL
4 NULL NULL
5 555 3
6 666 4
7 NULL NULL
8 777 5
Thanks a bunch.
September 29, 2011 at 4:33 am
You can use 'union' to achieve ,like null union non-null
here are test sql_code below:
create table test
(
col1 int,
col2 int
)
insert into test
select 1,null
union
select 2,2
union
select 3,3
union
select 2,null
union
select 4,1
select *,ROW_NUMBER() over ( order by col1)as number from test where col2 is not null
union
select *,null as number from test where col2 is null
September 29, 2011 at 4:35 am
SELECT * ,
ROW_NUMBER() OVER ( ORDER BY ID ) RowNum
FROM #test where colC is not null
union
SELECT * ,
null as RoeNum
FROM #test where colC is null
September 29, 2011 at 4:43 am
yubo1 (9/29/2011)
SELECT * ,ROW_NUMBER() OVER ( ORDER BY ID ) RowNum
FROM #test where colC is not null
union
SELECT * ,
null as RoeNum
FROM #test where colC is null
The above is the basic principal, but you should not use SELECT * and also use UNION ALL rather than UNION. e.g.:
SELECT ID ,
colC ,
ROW_NUMBER() OVER ( ORDER BY ID ) RowNum
FROM #test
WHERE colC IS NOT NULL
UNION ALL
SELECT ID ,
colC ,
NULL RowNum
FROM #test
WHERE colC IS NULL
September 29, 2011 at 4:47 am
yubo1 (9/29/2011)
You can use 'union' to achieve ,like null union non-nullhere are test sql_code below:
create table test
(
col1 int,
col2 int
)
insert into test
select 1,null
union
select 2,2
union
select 3,3
union
select 2,null
union
select 4,1
select *,ROW_NUMBER() over ( order by col1)as number from test where col2 is not null
union
select *,null as number from test where col2 is null
Thanks guys for the brilliant and simplistic solution!
September 29, 2011 at 4:50 am
yubo1 (9/29/2011)
SELECT * ,ROW_NUMBER() OVER ( ORDER BY ID ) RowNum
FROM #test where colC is not null
union
SELECT * ,
null as RoeNum
FROM #test where colC is null
Is there anything wrong by use '*' instead of 'ID,colc'?
table #test have not any other columns ...
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply