Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Merging data in a single SQL table without a Cursor

I have a table with an ID column and another column with a number. One ID can have multiple numbers. For example

ID | Number
 1 |  25
 1 |  26
 1 |  30
 1 |  24
 2 |  4
 2 |  8
 2 |  5

Now based of this data, in a new table, I want to have this

ID | Low | High 
1  |  24 |  26
1  |  30 |  30
2  |  4  |  5
2  |  8  |  8

As you can see, I want to merge any data where the numbers are consecutive, like 24, 25, 26. So now the low was 24, the high was 26, and then 30 is still a separate range. I am dealing with large amounts of data, so I would prefer to not use a cursor for performance sake (which is what I was previously doing, and was slowing things down quite a bit)...What is the best way to achieve this? I'm no SQL pro, so I'm not sure if there is a function available that could make this easier, or what the fastest way to accomplish this would be.

Thanks for the help.

like image 336
Andrew Backes Avatar asked Jan 02 '13 16:01

Andrew Backes


People also ask

How do I combine tables into one in SQL?

The simplest way to combine two tables together is using the keywords UNION or UNION ALL. These two methods pile one lot of selected data on top of the other. The difference between the two keywords is that UNION only takes distinct values, but UNION ALL keeps all of the values selected.

How can I MERGE two table data?

You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows.

Which is better cursor or while loop in SQL Server?

Always confusing thing is which one is better; SQL While loop or cursor? While SQL While loop is quicker than a cursor, reason found that cursor is defined by DECLARE CURSOR. Every emphasis of the loop will be executed inside system memory and consuming required server assets.


1 Answers

The key observation is that a sequence of numbers minus another sequence is a constant. We can generate another sequence using row_number. This identifies all the groups:

select id, MIN(number) as low, MAX(number) as high
from (select t.*,
             (number - ROW_NUMBER() over (partition by id order by number) ) as groupnum
      from t
     ) t
group by id, groupnum

The rest is just aggregation.

like image 66
Gordon Linoff Avatar answered Oct 05 '22 06:10

Gordon Linoff