|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
Code:
select BuyerID, foo, bar from data as d where foo in (select min(foo) from data where buyerid = d.buyerid) |
|
#4
|
|||
|
|||
|
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. |
![]() |
| Viewing: Dev Shed Forums > Databases > MS SQL Development > query help |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|