Professional Web Applications Themes

one table - no dups in one column - Microsoft SQL / MS SQL Server

Hope I can word this right.. One table contains multiple columns with a column(itemid) that contains duplicates. How can I return all columns and rows with the exception of rows with distinct on just column(itemid). ie. master table itemid descript retail size 808 tshirt 9.99 xl 706 tshirt 10.99 l 808 thsirt 9.99 s return itemid descript retail size 808 tshirt 9.99 xl 706 tshirt 10.99 l Any help appreciated...

  1. #1

    Default one table - no dups in one column

    Hope I can word this right.. One table contains multiple columns with
    a column(itemid) that contains duplicates.

    How can I return all columns and rows with the exception of rows with
    distinct on just column(itemid).

    ie.
    master table
    itemid descript retail size

    808 tshirt 9.99 xl
    706 tshirt 10.99 l
    808 thsirt 9.99 s


    return
    itemid descript retail size

    808 tshirt 9.99 xl
    706 tshirt 10.99 l

    Any help appreciated
    mark Guest

  2. #2

    Default Re: one table - no dups in one column

    What rule do you use for breaking a tie in this case, i.e. there are two items for itemid 808 and you have picked the one with size xl. Why not size s?

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "mark" <com> wrote in message news:google.com...
    Hope I can word this right.. One table contains multiple columns with
    a column(itemid) that contains duplicates.

    How can I return all columns and rows with the exception of rows with
    distinct on just column(itemid).

    ie.
    master table
    itemid descript retail size

    808 tshirt 9.99 xl
    706 tshirt 10.99 l
    808 thsirt 9.99 s


    return
    itemid descript retail size

    808 tshirt 9.99 xl
    706 tshirt 10.99 l

    Any help appreciated

    Tom Guest

  3. #3

    Default Re: one table - no dups in one column


    It doesn't matter which row is returned.

    From this return I then do a select on any itemid which has a catagory
    of 'clothing' (not shown) and collect all the sizes that itemid has and
    present it to the web user in one cell.

    I was just wanting one itemid to present one line with a picture to the
    web user with the ability to select a size link in that line to add to
    their cart.

    item descript retail click to enlarge add to cart
    808 thsirt 9.99 [thumbnail] xl s

    I'm sure there is a better way. But I'm doing my own web site for my
    business and have limited time to learn in depth.

    Thanks


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    mark Guest

  4. #4

    Default Re: one table - no dups in one column

    I still don't understand the real requirement. Are you looking for:

    select distinct
    itemid
    , descript
    , retail
    from
    MyTable



    --
    Tom

    ----------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql
    ..
    "mark howard" <com> wrote in message news:phx.gbl...

    It doesn't matter which row is returned.

    From this return I then do a select on any itemid which has a catagory
    of 'clothing' (not shown) and collect all the sizes that itemid has and
    present it to the web user in one cell.

    I was just wanting one itemid to present one line with a picture to the
    web user with the ability to select a size link in that line to add to
    their cart.

    item descript retail click to enlarge add to cart
    808 thsirt 9.99 [thumbnail] xl s

    I'm sure there is a better way. But I'm doing my own web site for my
    business and have limited time to learn in depth.

    Thanks


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Tom Guest

Similar Threads

  1. add column to table with sql
    By cf_chick in forum Coldfusion Database Access
    Replies: 3
    Last Post: May 25th, 06:49 PM
  2. Add a column of values to a table
    By Sebastian, CHILE ... in forum ASP.NET Web Services
    Replies: 1
    Last Post: February 3rd, 06:42 PM
  3. Insert into same table pulling one column from another table
    By Fred in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 12th, 09:54 PM
  4. Help! What is the correct way to insert without dups...(see msg)
    By Craig in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: August 8th, 09:11 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139