Friday 26 September 2008

How to generate ROWNUM in SQL Server SELECT

It is often required to create unique values for every row in a result set query in SQL Server. But SQL Server doesn’t have any built in functions such as ROWNUM in oracle to support this feature. There are many ways in which we can generate unique numbers in the select query as shown in the below scenario

Consider the Employee table with EmployeeName and EmployeeID columns and the scenario is to select records from the table based on EmployeeID with uniqueid for each row in the result set. The select statement can be written in below ways


Create table Employee ( EmployeeID INT , EmployeeName Varchar(30))
ROWNUM using ROW_NUMBER
SELECT ROW_NUMBER () OVER (ORDER BY EmployeeID) AS RowNumber, EmployeeName
FROM Employee ORDER BY EmployeeID

ROWNUM using IDENTITY
SELECT IDENTITY(int, 100, 1) AS RowNumber , EmployeeName
INTO #tmp
FROM Employee ORDER BY EmployeeID
SELET RowNumber , EmployeeID FROM #tmp

ROWNUM using NEWID
SELECT NEWID() ,AS RowNumber, EmployeeName FROM Employee
ORDER BY EmployeeID

Related article:
http://samsudeenb.blogspot.com/2009/06/how-to-generate-sequence-number-in-sql.html



25 comments:

b said...

Very useful. Thank you!

Anonymous said...

very good examples...
well done ;-)

Anonymous said...

It works great! Thank you!

deepika shukla said...

Thanks a lot!!!!!!!!!!!

Unknown said...

Thanks, it works great!

Anonymous said...

It would not work like Oracle rownum, as all your tricks require Order by or do not provide sequential IDs (NEWID()).
Oracle rownum used to enumerate rows sequentially and then use it for sorting or filtering.

Paul said...

Thanks for this.. could you provide an example of how to use it in a WHERE clause? Eg:
SELECT * FROM EmployeeName WHERE ROWNUM < 2 ORDER BY EmployeeID

Anonymous said...

top [url=http://swedeninternetcasino.com/]online casinos[/url] hinder the latest [url=http://www.casinolasvegass.com/]free casino bonus[/url] autonomous no set aside bonus at the foremost [url=http://www.baywatchcasino.com/]casino online
[/url].

Anonymous said...

[url=http://www.23planet.com]Online casinos[/url], also known as accepted casinos or Internet casinos, are online versions of vital ("chunk and mortar") casinos. Online casinos approve gamblers to extemporize and wager on casino games only the Internet.
Online casinos superficially tender odds and payback percentages that are comparable to land-based casinos. Some online casinos bring out prominence to on higher payback percentages against notify gismo games, and some piece the breathe anent payout enamour audits on their websites. Assuming that the online casino is using an fittingly programmed unsystematic plentiful generator, note games like blackjack enthral warrant an established van row edge. The payout incise up as a replacement representing these games are established at knee-jerk the rules of the game.
Uncountable online casinos sublease out or convoke their software from companies like Microgaming, Realtime Gaming, Playtech, Worldwide Temerarious Technology and CryptoLogic Inc.

Anonymous said...

Margaret is in my weekend "healthy eating" class and I've noticed quite a difference in her own confidence over the past 2 weeks. One quick method to get right into a dieting groove and turn into there is to bring the remainder of the family together with you phen375 enjoyment and habit in the past converted into a dependancy for the nicotine found in cigarettes and as with any addict, the individual goes to avoid those who constantly nag these phones quit smoking. Because of our cultures obsession with thinness, it is increasingly difficult for individuals to see the beauty and diversity in various sizes and shapes. This isn't Hudson's fault, exactly -- blame the world of spin at large http://www.phen375factsheet.com the idea here is to eat an apple before meal for they will assist you to feel full. Tip #3 Lessen Your Alcohol Intake - Drinking an excessive amount of alcohol can cause dehydration [url=http://www.phen375factsheet.com]phen375[/url] the american college of sports medicine notes that it normally takes just as much as 90 minutes of physical activity performed five days a week to slim down.

Anonymous said...

Hey are using Wordpress for your blog platform?
I'm new to the blog world but I'm trying to get started and create my own.
Do you require any coding knowledge to make your own
blog? Any help would be greatly appreciated!
Feel free to surf my blog post - slots online for money

Anonymous said...

I really like what you guys are up too. This kind of clever work and coverage!
Keep up the terrific works guys I've incorporated you guys to my personal blogroll.
Here is my page :: real money slot machine

Anonymous said...

This design is wicked! You certainly know how to keep a reader
amused. Between your wit and your videos, I was almost moved to start
my own blog (well, almost...HaHa!) Fantastic job. I really enjoyed what you had to say, and
more than that, how you presented it. Too cool!
Also visit my site Make Money From Home Online

Anonymous said...

Hi, i think that i noticed you visited my website so i came to go back the want?
.I am attempting to to find issues to improve my website!
I guess its good enough to make use of a few of
your concepts!!

Feel free to visit my weblog - online casino ohne einzahlung bonus

Anonymous said...

Hi, I do think this is a great blog. I stumbledupon it ;) I
will return yet again since i have bookmarked it.

Money and freedom is the greatest way to change,
may you be rich and continue to guide other people.


Look at my site ... new Online casinos

Anonymous said...

Howdy! This article could not be written
any better! Looking at this post reminds me of my previous roommate!
He continually kept talking about this. I'll forward this information to him. Fairly certain he will have a good read. Thank you for sharing!

Visit my web blog ... no Deposit microgaming casino bonus
My webpage :: latest microgaming casino bonuses

Anonymous said...

Quality content is the main to interest the users to
visit the web page, that's what this site is providing.

My web blog - betonsoft casinos no deposit bonuses

Anonymous said...

If you are going for best contents like I do, just pay a quick visit this website everyday for the reason that
it presents quality contents, thanks

Also visit my blog post - forex binary options

Anonymous said...

Ahaa, its pleasant dialogue about this article at
this place at this weblog, I have read all that, so at
this time me also commenting at this place.

Feel free to visit my website; easy legal ways to make money

Anonymous said...

Hi mates, its wonderful article regarding educationand entirely explained,
keep it up all the time.

Look at my site ... ways to make money online at home

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03

داليا said...

http://www.prokr.net/2016/09/bed-bugs-5.html
http://www.prokr.net/2016/09/bed-bugs-4.html
http://www.prokr.net/2016/09/bed-bugs-3.html
http://www.prokr.net/2016/09/bed-bugs-2.html
http://www.prokr.net/2016/09/bed-bugs.html

yanmaneee said...

curry 6 shoes
yeezy
nike epic react
goyard
pandora jewelry
golden goose sneakers
off white outlet
hermes
yeezy shoes
yeezy shoes