Friends, most of the time we used ranking function like ROW_NUMBER() when it required to generate unique number sequence. But there also other ways to accomplish the same thing without using ranking function. I will tell you 3 various ways to generate the unique number sequence including ranking function.
Let’s create a demo table with some dummy data:
CREATE TABLE Months
INSERT INTO Months
SELECT 101, ‘January’
SELECT 102, ‘February’
SELECT 103, ‘March’
SELECT 104, ‘April’
SELECT 105, ‘May’
SELECT 106, ‘June’
SELECT 107, ‘July’
SELECT 108, ‘August’
SELECT 110, ‘October’
SELECT 111, ‘November’
SELECT 112, ‘December’
Here, are the below 3 different methods to generate the unique numbers.
SELECT MonthId, Mon_Name, ROW_NUMBER() OVER (ORDER BY Mon_Name) rnum
SELECT MonthId, Mon_Name, COUNT(*) OVER (ORDER BY MonthId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)as rnum
SELECT MonthId, Mon_name, (SELECT COUNT(*) FROM months m1 WHERE m1.MonthId <= m2.MonthId) as rnum
FROM months m2
ORDER BY rnum
After executing these queries we will get the sequence of unique number. We can choose any method according to our choice to generate numbers sequence.
That’s all for the day folks. Keep learning 🙂
HI friends, in some working scenario or during interviews we got the task to find the running totals of a column. In this blog I have listed few methods which may help you in determining running totals:
Let’s first create a test table and insert some data into it.
Create table #t1 (ID int)
INSERT INTO #t1 values (1), (2), (3)
SELECT ID, SUM(ID) OVER(Order by ID ROWS BETWEEN unbounded preceding
and current row) run_tot
SELECT A.ID,SUM(B.ID) run_tot
FROM #t1 A
JOIN #t1 B ON B.id <= A.id
GROUP BY A.ID
You will get the same output from Method 1 and Method 2.
That’s all folks for the day. Have a good day!!
Hi friends, I was working on a scenario in which I need to assign a unique values to a column. I was trying to update the column using window ranking function ROW_NUMBER() but I got error that ‘Windowed function can only appear in SELECT or ORDER BY clause’.
Then I do some workaround and used the windows function indirectly using a CTE (Common Table Expression) for this. I will show you steps by steps how I accomplished this using CTE.
Let’s first create a table with some test data:
CREATE TABLE Test
Value VARCHAR(10) NOT NULL
INSERT INTO Test (Value) VALUES(‘Sunday’),(‘Monday’),(‘Tuesday’),(‘Wednesday’),(‘Thursday’),(‘Friday’),(‘Saturday’)
As we can see that in column ID NULL values gets inserted as we didn’t specify any values for this column during INSERT statement. So when I tried this UPDATE statement using ROW_NUMBER() with SET I got the following error:
SET ID = ROW_NUMBER() OVER(ORDER BY Value)
Then I used CTE to update the ID values in table Test in this manner:
;WITH CTE AS
SELECT Value, ROW_NUMBER() OVER(ORDER BY Value) as RN
SET ID = RN
FROM CTE C JOIN Test T ON T.value = C.Value
When I run this sql code I didn’t get any error and ID column values get updated with unique values.
That’s all folks. Hope you like this post.