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

i'm your host sean mckenzie thank you for joining me once again on my channel on data engineering in this episode we return to our data engineering playlist and we're going to look at how to do a query where we put a row number down the side of our query so that we have an idea of you know how things are ordered or what the what the row number is for each of the items in our query and the question is how can we add that uh in a simple way and also how can we group things together and have the row order of our query within those groups as well and to do that we're going to use row number over and we're also going to use partition by and so let's get to it need help for coaching on your project make sure to check out my patreon the link is in the description okay so i'm using sql server management studio here and i'm just going to open the top thousand records for this part table that i have this fictional part table and it only has 16 rows in it but it does have some parts in it and we can demonstrate how to add a row number uh to our query you know starting from one and uh and we can do that you can see it's got part numbers and it has a parent part number so you can see you know you know that there's some parts belong to other assemblies and things like that so it's a self-joining table and we're going to use that as part of our demonstration here for adding a row number and what i could do is i could you know just select the part number from that manufacturing table and i could order by which you're probably familiar with i could order by the part number and if i ran that you can see you know it'll put the part numbers in order aa11 and then aab111 etc etc and it puts them all in in alphabetical order but what i wanna see is i wanna see you know like we have down the left-hand side there even though that's part of the that's actually you know the one two three four five you see there is not part of the query that's actually part of the you know the results window but what we can do is we'll add a field called part order you can see if i wanted to add it there kind of similar to what you see in the results window but i actually want it as part of my query well in order to do that what i could do is um you know i can take this first of all i'll take out this order by from here i'll cut that because we're going to use it again and we're going to add another field and we're going to use row underscore number for that and then when you use row underscore number you need to use the over it also uses the the keyword over and then you you need to put in the order by inside of the over and and then we'll give that a part name or i guess part order we'll call it like we had before there so now we've got this this field using row number over and then order by part number and we'll call that part order and if i run that then you can see now i've got this nice row or pardon me column that has our our output in order of the part number and that's exactly what we wanted to see and you know if i wanted to you know do that for another field a different field say well i can do that as well so i can take this you know this row here and i can copy and paste that and what i'll do in this case is i'll uh change the uh the the the column that i'm going to use i'll use part name instead and you know if i just ran that well that's not going to make any sense um because i need to also order by the part number so this is ordering by the part number uh use for our column so you need to make sure that you put in the part part name as well so that it actually orders it by the part name and and then it will also give the numbering the row number according to the part name so that's that's um that's a really um important thing that you need to do and uh we can also you know if i wanted to what if i wanted to do the parent part number well i could do that too so i could do you know i could take the parent part number and that's going to have duplicates of course because it's going to you know there's going to be multiple times that the parent part number is used while you know all of its children are there so we can go uh you know we could just go select parent part number part number and then order by the those and you can see here we go it's a one one uh one with aaa and aab so that's gonna be one and two and then this one is aab so it'll be one and two and those are grouped kind of grouped together because of the the ordering and so so what if we wanted to say you know i want to see the order within the parent because that's very important too so you'll probably do that all the time in queries once you sort of learn how to do this and so what we're going to do is we're going to you know leave our parent part number and part number fields in there and then we'll use our row number over again just like we did there and you know just like we did before we'll you know put our order by in there and we're going to order by the the parent part number because we want to order it by the parent part number so those are sort of grouped together and if i just run that well it doesn't kind of make sense because we still have you know one two three five all the way to nine but we don't have the you know the breakup of the ordering that we wanted so i'll add the part order name to that field and and so what we need to do is we need to first of all shorten this because we're we're kind of turning into spaghetti sql there uh so uh then what we need to do then is we're going to add the partition by um and partition by is going to tell our you know our function here that it's going to it's going to partition by that field the parent part number field and and then and then order by the part number within that and so then it's going to give us our one you know one two three and then it'll restart numbering when it hits the next parent part number then it'll go one two three or whatever there might not be that many in this particular table because there's very few rows but i think it'll show some so let's just take that and run it and as you can see yeah there we go so we got one and then those two that i showed you this one has two uh two children under the parent so it goes one two and this one also has two children under the parent and it goes one two and so on and so forth so if you you could have you know hop 200 or whatever it would number them within each group and that's exactly what we want to see and that is how you can add a row number to your query in sql hope you enjoyed today's discussion on how to do uh row number in your query if you like what you saw today give the video a thumbs up subscribe to the channel make sure to click the bell when you see the bell and put any questions or comments in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 140

Keywords: how to add a row number to your query in sql using row number and partition by, sql add row number, sql row number, sql partition by, tsql row number, sql server, partition by, sean mackenzie data engineering

Id: HYeZKS9F2b0

Length: 8min 32sec (512 seconds)

Published: Sat Nov 20 2021