locked
Is this something possible in SQL? RRS feed

  • Question

  • Hello,

    Let's say you have following table and can't change your table structure

    Name, Data

    abc,    1

    def,    3

    aabbcc, 2

    xyz,    7

    jio,    4

    qwer,   3


    You need to sort by Data one by one like this...

    abc,    1

    aabbcc, 2

    def,    3

    jio,    4

    xyz,    7

    qwer,   3

    Is this even possible only by SQL?

    If row data has range from 1 to 7, then that sorted data should be 1,2,3,4,5,6,7,1,2,3,4,5,6,7...something like that..

    The order of same number doesn't matter as long as they are in this sequence.

    Thanks

    Tuesday, February 19, 2013 8:47 PM

Answers

  • You can do this with a cte and the ROW_NUMBER() window function:

    Declare @tvTable Table (
    	Col1	varchar(10)
    	,SomeID	int
    )
    Insert	@tvTable
    Values	('abc',    1)
    		,('def',    3)
    		,('aabbcc', 2)
    		,('xyz',    7)
    		,('jio',    4)
    		,('qwer',   3)
    
    Select	*
    From	@tvTable
    
    ;with cte as
    (
    	Select	Col1
    			,SomeID
    			,ROW_NUMBER() OVER(Partition By SomeID Order by SomeID) RowNumber
    	From	@tvTable
    )
    Select	Col1
    		,SomeID
    From	cte
    Order By RowNumber, SomeID

    Tuesday, February 19, 2013 8:58 PM