number question

Question

i hava a table below

number

8789789
9080992
3213123
2143123
1312321
.......
.......
1232123

almost 50.000 rows

i want to get ordered numbers like

856620
856621
856622
856623
856624
856625
856626
856627
856628
856629

or

216350
216351
216352
216353
216354
216355
216356
216357
216358
216350

i want to get from table grouping 10 numbers in a series.

finally i shoul be an array like below

array[ [856620, 856621, 856622, 856623, 856624, 856625, 856626, 856627, 856628, 856629 ], [ 216350, 216351, 216352, 216353, 216354, 216355, 216356, 216357, 216358, 216350,

] ]

how is that posibble?

Answer 1

select number from yourtable order by number

Answer 2

Can't you just use ORDER BY number?

That would give you all numbers in order.

Answer 3

its not simple like your answers :)

i want to get from table grouping 10 numbers in a series.

finally i shoul be an array like below

array[
[856620,
856621,
856622,
856623,
856624,
856625,
856626,
856627,
856628,
856629
],
[
216350,
216351,
216352,
216353,
216354,
216355,
216356,
216357,
216358,
216350,

]
]

Answer 4

If it's MSSQL try

SELECT TOP 10 number FROM table WHERE number > 21649 ORDER BY number ASC

will get you

21650 21651 21652 21653 21654 21655 21656 21657 21658 21659

For MySQL try

SELECT number FROM table WHERE number > 21649 ORDER BY number ASC LIMIT 10

Answer 5

select n1.number, n2.number, n3.number, n4.number, n5.number, n6.number, n7.number, n8.number, n9.number, n10.number
from tablename n1
inner join tablename n2 on n1.number=n2.number-1 
inner join tablename n3 on n2.number=n3.number-1
inner join tablename n4 on n3.number=n4.number-1 
inner join tablename n5 on n4.number=n5.number-1 
inner join tablename n6 on n5.number=n6.number-1
inner join tablename n7 on n6.number=n7.number-1
inner join tablename n8 on n7.number=n8.number-1 
inner join tablename n9 on n8.number=n9.number-1
inner join tablename n10 on n9.number=n10.number-1

But surely there's a better way to solve your underlying problem.

Answer 6

Your requirement isn't entirely clear based on the information provided so far.

Assuming you're on SQL Server 2005/8, the following code will give you a result set with two additional columns, arrayNo (which provides a unique identifier for each array) and ord, which shows the position of the number within the array.

select number
       ,number/10                                            AS arrayNo
       ,ROW_NUMBER() OVER (PARTITION BY number/10
                            ORDER BY number
                          )                                  AS ord
from <tablename>
where number between <rangeStart> and <rangeEnd>

However, if you want each row in the resultset to represent an array, the following code is a modification of the version above, which pivots the result set into columns.

;with arrCTE
AS
(
        select number
               ,number/10                                            AS arrayNo
               ,ROW_NUMBER() OVER (PARTITION BY number/10
                                    ORDER BY number
                                  )                                  AS ord
        from <tablename>
        where number between <rangeStart> and <rangeEnd>
)
select * from arrCTE
pivot (MAX(number) FOR ord in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) AS p

Answer 7

thanks for all helps

i have decided to solution in application code.

my code is below.

        var numbers = DB.Numbers.OrderBy(l => l.Number).ToList();

        List<int> series;
        List<List<int>> allSeries = new List<List<int>>();
        int seriesCount = 10;


        for (int i = 0; i < numbers.Count; i++)
        {
            if (numbers[i].Number % seriesCount == 0)
            {
                series = new List<int>();

                series.Add(numbers[i].Number);

                for (i=i+1;i<=i+seriesCount-1;i++)
                {
                    if (numbers[i].Number == series.Last() + 1)
                    {
                        series.Add(numbers[i].Number);
                    }
                    else
                    {
                        break;
                    }
                }

                if (series.Count == seriesCount)
                {
                    allSeries.Add(series);
                }                    

            }

        }

Answer 8

Here is a version which uses Linq exclusively:

private IEnumerable<List<int>> Partition(IEnumerable<int> series, int groupSize)
{
    // Associate each number with its index in the ordered set

    var indexedNumbers = series
        .OrderBy(number => number)
        .Select((number, index) => new { Number = number, Index = index });

    // Group the numbers using groupSize, and for each group, order and
    // convert to a list

    return
        from indexedNumber in indexedNumbers
        group indexedNumber by indexedNumber.Index / groupSize into indexedNumberGroup
        select indexedNumberGroup
            .Select(indexedNumber => indexedNumber.Number)
            .OrderBy(number => number)
            .ToList();
}

I just realized it won't translate to SQL because of the usage of the Select() overload with an index, but since you have opted to solve it in application code anyways, this will work for you as Linq to Objects.