October 11, 2011 at 11:29 am
If one (or more) field(s) listed in Partition By have nulls - how ROW_NUMBER () handles these records? Are they (records) INcluded into the qry result or EXcluded?
Thanks
October 11, 2011 at 11:43 am
Not exactly sure what your question is but the following may help answer it for you.
with sampledata(Value, CanBeNull) as
(
select 1, 1
union
select 2, 1
union
select 3, null
union
select 4, null
)
select ROW_NUMBER() over(partition by CanBeNull order by value),* from sampledata
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 11:57 am
Not exactly. I need to get rid of duplicates in the existing table. I am using four fileds in the partition by list. One of these fields in some records has null value.
*******************************
SELECT [SITE]
,[Hospital]
,[LOCATION]
,[Real_Date_Time]
,[NURSE_ID]
,[PRIMARY_NAME]
,[ERROR_CODE]
,[PMP]
,[PAT_NUM]
,[UniqueID]
,[PTNAME]
,[SCANNED_DATA]
,[DRUG_DESC_SCAN_ERR]
,[Sig]
,[Sig_Desc]
,[Frequency]
,[SCH_PRN]
,ROW_NUMBER() over (partition by
datepart(hour,real_date_time)
,nurse_ID
,pat_num
,pmp <== may be null
,ERROR_CODE
order by
datepart(hour,real_date_time)
,nurse_ID
,Pat_num
,pmp
,ERROR_CODE) rNum
FROM LoadTblAllDatanoDuplicates, etc
****************************************
So can the null value in the record prevent it from listing?
October 11, 2011 at 12:00 pm
This do it?
where MyFieldThatHasNullsThatIDontWant is not null
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 12:03 pm
I need this record (with null in the field) to be included.
October 11, 2011 at 12:07 pm
And (out of the topic!) how to place the code into that nice lookink box as you do?
October 11, 2011 at 12:09 pm
Are saying you are partitioning by 5 fields but if it has a null it should make it part of some other group? Which group would it belong to? That is what partitioning does. It partitions the records into groups based on like values in a field, meaning that for like values it will reset the current row number.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 12:10 pm
Actually after some experimenting I am convinced that null-containing records ARE included in the result set
October 11, 2011 at 12:11 pm
valeryk2000 (10/11/2011)
And (out of the topic!) how to place the code into that nice lookink box as you do?
There are a couple ways to do it. When typing, look over on the left, there a lot of things you can add.
I usually just type it though.
[ code]
if you remove the spaces from this tag it will look this box.
[ /code]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 12:12 pm
valeryk2000 (10/11/2011)
Actually after some experimenting I am convinced that null-containing records ARE included in the result set
Yes, that is what I showed you in my first post. Just because a column is null the count is still there.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 11, 2011 at 12:15 pm
It should belong to the same group. I just was not sure on null values behaviour in the ranking function.
Thanks!
October 11, 2011 at 12:17 pm
Try Dense_Rank instead. Let me know if it work. Selecting distinct dense rank using CTE.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 11, 2011 at 12:20 pm
Thanks!
October 11, 2011 at 12:29 pm
Result with DENSE_RANK () in my code is the same as with ROW_NUMBER()
October 11, 2011 at 12:32 pm
can u please provide me 3 records with columns included in ranking function
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply