Post reply

Data Sort

  • May 28, 2004 at 6:47 am

    #111541

    Hello everyone,

    I've been asked to return a result set of the following:  We need to get a result set of every first and fifth record after that are = to 'Cache Data'.  records two, three and four and after would = to 'Centura Data'.  There is almost 40,000 records in the table.

    Here is an example of the 'existing' result set:

    Cached Data
    Cached Data
    Cached Data
    Cached Data
    Cached Data
    Cached Data
    Cached Data
    Centura Data
    Centura Data
    Centura Data
    Centura Data
    Centura Data
    Centura Data
    Centura Data
    Centura Data

    Centura Data

    Here is an example of the 'expected' result set:

    Cached Data
    Centura Data
    CenturaData
    CenturaData
    Cached Data
    Centura Data
    Centura Data
    Centura Data
    Cached Data
    Centura Data
    Centura Data
    Centura Data
    Cached Data
    Centura Data
    Centura Data

    Centura Data

    Is there a specific function in SQL Server to do this or am I going to have to write a program to display the 'Cache Data', every 1st and fifth record and the 'Centura Data' every 2nd 3rd and 4th record?

    Any help you can provide me with would be greatly appreciated.

    Thank you,

    Pete..

    Peter M. Florenzano

    Database Administrator

  • May 28, 2004 at 7:43 am

    #508196

    Anyone?

    Peter M. Florenzano

    Database Administrator

  • May 28, 2004 at 7:55 am

    #508202

    Hi Pete,

    I'm not sure I actually understand the problem well enough.  Can you post the table strucutre you are querying, and perhaps the query itself?

    Wayne

  • May 28, 2004 at 8:06 am

    #508212

    Hi Wayne,

    Thank you for your reply.  We are attempting to establish test data from two different sources 'Cache' and 'Centura'.  We have the table struture, with the test data.  The table does have an identity column.

    I haven't written the query because I'm unsure where to start with this.  Every 1st and 5th, 9th, 13th, etc, etc record would be equal to the source(column name) = 'Cache Data'. Records 2, 3, 4, 6, 7, 8, 10, 11, 12, etc, etc would be equal the source value 'Centura Data'.

    Any ideas would be much appreciated.

    Thanks!

    Peter M. Florenzano

    Database Administrator

  • May 28, 2004 at 11:24 am

    #508259

    What you asked for is kind of confusing....let me try to reword your question and you let us know if this is what you want....

    You want a query that returns the first occurance of CACHE (result row 1), then skips all other occurances of CACHE until CENTURA is found, then return that (result row 2) and the next two occurances of CENTURA (result rows 3 &4), then go back to the beginning, skip the first occurance of CACHE and find the next occurance of CACHE and return it as result row 5. Then skip all previous returned rows of CENTURA and find the next three rows of CENTURA and return them as result rows 6, 7, and 8....etc.

    Is that what you are looking for?

    If so, I think we are going to need more of the table structure...like the identity column.

    -SQLBill

  • May 31, 2004 at 3:05 am

    #508356

    is this you want ?

    SELECT IDENTITY(INT,1,1) AS IdCol INTO #A FROM YOUR_TABLE

    SELECT CASE WHEN IdCol % 4 = 1  THEN 'Cached Data '  ELSE 'Centura Data ' END FROM #A

    ORDER BY IdCol

    DROP TABLE #A


    -- Amit


    "There is no 'patch' for stupidity."


    Download the Updated SQL Server 2005 Books Online.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply