Apply Row Number Function on Distinct Value

Sometime we need to apply the Row Number function on Distinct value .
Here is the example :

Declare @Table Table (COL1 Int , COL2 Int)

Insert Into @Table
Select 100, 5000
UNION ALL
Select 101, 5000
UNION ALL
Select 102, 5000
UNION ALL
Select 100, 7000
UNION ALL
Select 102, 8000
UNION ALL
Select 103, 9000

Suppose here we want to just list all values from COL1 with a extra Row Number column .
Output should be like :

Col1	Sr.No
100	1
101	2
102	3
103	4

To generate the Sequence number we need to use Row_Number function .

---- SIMPLE
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
---Output

COL1 RN
-----------
100 1
100 2
101 3
102 4
102 5
103 6

Here we generate the Sequence Number for COL1, ohh but what is this happening , sequence number is also generating for duplicate records means for all the records of COL1.

Lets try use the DISTINCT to get the unique values:

---- WITH Distinct
Select DISTINCT COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table

--- Output
COL1       RN
-----------
100         1
100         2
101         3
102         4
102         5
103         6

Output is same again.

Lets give a chance to GROUP BY clause ..

---- WITH Group By
Select COL1, ROW_NUMBER() Over(Order By COL1) As RN
From @Table
Group By COL1
--- Output
COL1        RN
----------- --------------------
100         1
101         2
102         3
103         4


Wow, its working now.
Means Group By works well with Row_Number Function.


I did not try where it can fails....

How to Alter Primary key ?

To modify a PRIMARY KEY constraint, we must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition. :)

How Dangerous Null values are in Subquery

Today I came across a strange problem while using simple subquery, everything was right but no data was returning.


here is that query :

Declare @Seats Table (SeatNumber Varchar(4))
Declare @ExcludeSeats Table (SeatNumber Varchar(4))

Insert Into @Seats
Select '44K'
UNION
Select '44J'


Insert Into @ExcludeSeats
Select '44K'
UNION
Select NULL


Select * from @Seats
Select * from @ExcludeSeats

Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats)

-------------------------------------------------------------------
First table containing seat Numbers 44K & 44J and other containing 44K and a Null value.
And now requirement was to find out the seats that are available in table Seats but does not exists in table ExcludeSeats, I wrote a very simple query using a subquery and  run . I was surprised to see no data.

However, one row should return.

After sometime I got the problem of NULL value returning from subquery and when I filtered the NULL value, worked fine .

The query was after modified :


Select * From @Seats
Where SeatNumber Not In (Select SeatNumber From @ExcludeSeats Where SeatNumber Is Null)

So today learning is that If a subquery returns NULL value, the query failed.


Thanks

Replace word in Excel using Function

In Excel, the Substitute function replaces a set of characters with another.
The syntax for the Substitute function is:

= Substitute( text, oldtext, newtext, nthappearance )

e.g.

In a word "Rajeev ", we want to replace "ee" with "i" then formula will be :


=SUBSTITUTE(A1,"ee","i")

nth appearance is optional. It is the nth appearance of old_text that you wish to replace. If this parameter is omitted, then every occurrence of old_text will be replaced with new_text.