Post reply

Query puzzled.

  • December 21, 2006 at 10:29 am

    #122082

    I need some help with a query to pull out a subset of data. Hopefully it'll be clear by example.

    My starting dataset is SET1; however, I only need a subset of it (i.e. SET2 or SET3).  For a given attribute1 and keys1, I want a unique col1 and col2 pairing (they can swapped).  Can someone desribe the query I need to pull this?

    SET1attribute1col1col2keys1
    val1ab1,2,3
    val1ba1,2,3
    val2xy7,8,9,10
    val2yx7,8,9,10
    SET2attribute1col1col2keys1
    val1ab1,2,3
    val2xy7,8,9,10
    SET3attribute1col1col2keys1
    val1ba1,2,3
    val2yx7,8,9,10

    Thanks!

  • December 21, 2006 at 11:21 am

    #679502

    SELECT DISTINCT Attribute1, CASE WHEN Col2 > Col1 THEN Col1 ELSE Col2 END AS Col1, CASE WHEN Col2 > Col1 THEN Col2 ELSE Col1 END AS Col2, Keys FROM dbo.YourTable

     

    That'll give you set2 everytime.

     

    May I suggest a unique constraint on col1, col2 so that this situation cannot happen again after you cleaned the data??

  • December 21, 2006 at 11:35 am

    #679504

    Great!  Results look good...

    The result is actually a derived set from system tables.  I've just been scratching my head how to get rid of those duplicates.  Should've posted this question here sooner...

    Thanks!

  • December 21, 2006 at 11:39 am

    #679505

    What table is that? sysindexes?!

  • December 21, 2006 at 11:48 am

    #679510

    It's related to indexes but I'm not querying the tables directly.  I'm trying to create a comprehensive index report.  I've seen readymade queries here, but I'm trying to avoid querying system tables directly, and cursors, plus including some aggregate and derived information as well.

  • December 21, 2006 at 11:55 am

    #679512

    k, good luck with that.

     

    Happy holidays .

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

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