Post reply

Help finding common rows

  • June 17, 2009 at 12:33 pm

    #135356

    I have a table as follows

    CREATE TABLE temp(

    itemnumber nchar(4),

    fname varchar(12),

    lname varchar(12),

    speed varchar(12),

    acc varchar(12),

    tyre varchar(12)

    PRIMARY KEY (itemnumber)

    )

    INSERT INTO temp VALUES (1111, 'john','cox','1.7k','1m','4n')

    INSERT INTO temp VALUES (1112, 'john','cox','1.7k','1m','4n')

    INSERT INTO temp VALUES (1113, 'john','cox','1.7k','2m','6n')

    INSERT INTO temp VALUES (1114, 'john','fed','1.7k','2m','6n')

    INSERT INTO temp VALUES (1115, 'john','fed','1.6k','2m','6n')

    INSERT INTO temp VALUES (1116, 'john','cox','1.6k','2m','6n')

    INSERT INTO temp VALUES (1117, 'john','fed','1.6k','2m','6n')

    INSERT INTO temp VALUES (1118, 'john','cox','1.7k','2m','6n')

    Now my problem is I want all the information in the following format

    |1111 |john |cox |1.7k |1m |4n |

    |1112 |john |cox |1.7k |1m |4n |

    |1113 |john |cox |1.7k |2m |6n |

    |1118 |john |cox |1.7k |2m |6n |

    |1114 |john |fed |1.7k |2m |6n |

    |1115 |john |fed |1.6k |2m |6n |

    |1117 |john |fed |1.6k |2m |6n |

    |1116 |john |cox |1.6k |2m |6n |

    Here we can see that rows with item number 1111 and 1112 are common, similarly 1113 and 1118 are common and so on.

    Basically I just want to get information in sorted order but have similar values.

    Can some body help?

    Thanks in advance.

  • June 17, 2009 at 4:40 pm

    #1011351

    Thanks for viewing. I have figured it out, but with additional work.

    Solution should be some what like this.

    SELECT fname, lname, speed,acc, tyre

    FROM temp

    GROUP BY fname, lname, speed,acc, tyre

    It will return unique rows from the table then we can use those values to get the item numbers.

  • June 17, 2009 at 8:02 pm

    #1011380

    You're making it harder than necessary.

    select * from temp

    order by speed desc, acc, tyre, fname, lname

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 17, 2009 at 9:54 pm

    #1011414

    Thanks you were right.

    I never thought about that.

    Thanks again.

Viewing 4 posts - 1 through 3 (of 3 total)

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