Thread: query help

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0

    query help


    I have a database of transaction data that includes "buyerID". Some buyers have bought multiple items within the time period that I'm looking at, but I just want one row (I don't care which row) for each unique buyerId.

    "select distinct BuyerID from data" gets me a list of the unique buyer Ids, but I don't know how to get only one row for each Buyer ID. When I do

    "select distict BuyerID, foo, bar from data" (foo and bar being other column names in the table) it return the original full data set.

    If any one has any advice on how to solve my problem, I'd be much obliged.

    Regards,
    jonb
  2. #2
  3. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0
    P.S. This is what I'd really like to be able to do

    select * from data where SellerID is distinct

    but it doesn't work
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Code:
    select BuyerID, foo, bar 
    from data  as d
    where foo in (select min(foo) from data
    where buyerid = d.buyerid)
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2003
    Posts
    3
    Rep Power
    0
    Thanks swampBoogie! I ended up doing this
    _________________________________
    insert into no_dupes_data
    select *
    from data as d
    where ItemID in
    (select min(ItemID) from data where BuyerId = d.BuyerId)
    __________________________________

    to grab the uniques and put them into a new table, and it seemed to work.

    min(ItemID) is obviously getting the lowest ItemID. Is there a function that would grab a random ItemID? Not a big deal, but I wasn't specifically looking for the lowest ItemID, so that's a minor pollution of the dataset.

IMN logo majestic logo threadwatch logo seochat tools logo