![]() ![]() Or if you leave out the partition by clause entirely, you get a single sequence of numbers starting with 1 and incrementing by 1 to the last row selected. ![]() Each group get's it's own sequence of numbers starting with 1. You could for example assign a new group of sequence numbers to each order, or as we have done above, to each order detail line. Partition by defines how to group the selected records. The clauses that you can put in the parenthesis of over() determine how the row numbers are assigned. Notice the syntax of ROW_NUMBER expression. The magic is in the inner subselect which returns a list of record ids with it's row_number with breaks ( partition by) on order_number and detail_line, sorted by ( order by) seq_number. Over(partition by order_number, detail_line But what if you want to do this with SQL? Well as I mentioned earlier you can use an OLAP feature of the database called ROW_NUMBER to do the same thing. This is pretty simple and performance is lightening fast. I might also want to reset back to 1 for each new detail_line and order_number. If I were using RPG and record level access methods, I could simply read the records in order, and reassign seq_number from a counter starting at 1 and continuing until the rows were read. Periodically this sort sequence gets gaps due to deletions, or other operations on the file, and we want to close those gaps up. So you can see this table has a primary key named id an order number, a detail line number, and a sequence number for sorting the records, among other fields not named. It is ROW_NUMBER that we are specifically interested in here. These are expressions you can use to query things like rank, dense rank, and row number. ![]() Every now and then I hear a question like "How can I reset the sequence numbers in my file using SQL?" If you are using DB2 for i, there is a simple solution involving OLAP specifications. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
March 2023
Categories |