How to Add a Row Number to Your SQL Query Using row_number and partition by

In this episode, we'll take a look at how to add a row number to our query so that we can refer to that ordering when we use the data for other purposes. In order to do this, we're going to use two very important features of sql: row_number and partition by. Using these, we'll create a row number column for the whole column, and then split up our data and give a row number inside of those groups.
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
For developers looking for a new role, check out:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Demo of my BZ RDP Cloaker:
www.patreon.com/posts/how-to-...
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a TH-cam Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
• How to Add a Row Numbe...

[@USA_to_Asia_in_2024]

GREAT explanation.

[@seanmackenziedataengineering]

Glad it was helpful!

[@sarahgorton8927]

very well explained, thank u!

[@seanmackenziedataengineering]

Glad it was helpful!

[@Oleemil89]

Nice video! I enjoyed the intro song, but people have different expectations i guess. Who is playing and what song is it? Had a nice riff

[@seanmackenziedataengineering]

I believe that track was called "Ripped" and was included with license as part of the Camtasia studio selections.. 2019 or 2020 version iirc. I thought it was groovy!

[@sonathena3150]

Hello Sean, do you have a copy of the dataset in this discussion? I hope you do have so we can try it by ourselves. Thanks

[@seanmackenziedataengineering]

Yes I'll take a look today and see! You will need to put it on your SQL Server. Thanks!

[@maheshs8069]

Sir, will these rownum work for table with million record

[@seanmackenziedataengineering]

I don't see why not! Give it a try. It may slow down though. At a point, you may want to just add a column to that table and make it persistent (and faster to work with).

[@anilmanuka2052]

How to update empty column a value in declare table

[@seanmackenziedataengineering]

If you are creating a new table, you can use a default to fill values that are not selected. If you wanted a date for example, you could make the field:
MyDateField datetime default GetDate()

[@diegovaldes3785]

is there a way that the row number continues the sequence after running the same query again?

[@seanmackenziedataengineering]

Great question. It should continue generating more numbers if you added rows to the table, then executed the query again. Is that what you mean?

[@diegovaldes3785]

@@seanmackenziedataengineering hmm let me give you an example: I made a query that selects data based on a search criteria and want a sequence number related to each one of the items of this data, then a process unrelated to SQL does things with that data so it does not fit with the search criteria. What I need is that, whenever new data comes in that fits the search data, the sequence number continues where it was left

[@alekkermd7775]

The background music is very distracting.

[@seanmackenziedataengineering]

Thanks for the feedback! All my newest videos removed the intro music.

[@USA_to_Asia_in_2024]

Check out the mirror and you'll see a crybaby who cries about music and should have bigger fish to fry