Posted in Development

SQL Server – Various methods to generate unique number sequence

Hi all,

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

(

MonthId INT,

Mon_Name VARCHAR(20)

)

GO

INSERT INTO Months

SELECT 101, ‘January’

UNION ALL

SELECT 102, ‘February’

UNION ALL

SELECT 103, ‘March’

UNION ALL

SELECT 104, ‘April’

UNION ALL

SELECT 105, ‘May’

UNION ALL

SELECT 106, ‘June’

UNION ALL

SELECT 107, ‘July’

UNION ALL

SELECT 108, ‘August’

UNION ALL

SELECT 109,’September’

UNION ALL

SELECT 110, ‘October’

UNION ALL

SELECT 111, ‘November’

UNION ALL

SELECT 112, ‘December’

Here, are the below 3 different methods to generate the unique numbers.

–Method 1

SELECT MonthId, Mon_Name, ROW_NUMBER() OVER (ORDER BY Mon_Name) rnum

FROM Months

–Method 2

SELECT MonthId, Mon_Name, COUNT(*) OVER (ORDER BY MonthId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)as rnum

FROM months

–Method 3

SELECT MonthId, Mon_name, (SELECT COUNT(*) FROM months m1 WHERE m1.MonthId <= m2.MonthId) as rnum

FROM months m2

ORDER BY rnum

Seq_1

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 🙂

Advertisements
Posted in Development

SQL Server – Methods to find running totals of a column

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)

GO

INSERT INTO #t1 values (1), (2), (3)

GO

–Method 1

SELECT ID, SUM(ID) OVER(Order by ID  ROWS BETWEEN unbounded preceding

and current row) run_tot

FROM #t1

–Method 2

SELECT  A.ID,SUM(B.ID) run_tot

FROM #t1 A

JOIN #t1 B ON B.id <= A.id

GROUP BY A.ID

Running_Totals

You will get the same output from Method 1 and Method 2.

That’s all folks for the day. Have a good day!!

Posted in Development

SQL Server – Using Window ranking function with UPDATE statement

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’.

Rank_1

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

(

ID INT,

Value VARCHAR(10) NOT NULL

)

GO

INSERT INTO Test (Value) VALUES(‘Sunday’),(‘Monday’),(‘Tuesday’),(‘Wednesday’),(‘Thursday’),(‘Friday’),(‘Saturday’)

GO

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:

UPDATE Test

SET ID = ROW_NUMBER() OVER(ORDER BY Value)

GO

Rank_2

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

FROM Test

)

UPDATE T

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.

Rank_3

That’s all folks. Hope you like this post.