Selecting top 3 distinct on certain fields

I'm trying to get a data set to bind to an ASP.NET control -- I want the TOP 3 most recent products ordered, but I DO NOT want to display duplicate customers or products.

My existing query is:

SELECT DISTINCT Top 3
      p.ID
      , p.ProductName
      , t.Amount
      , u.FirstName
      , t.TransactionID
      , t.CreatedOn
FROM Transactions t
INNER JOIN TransactionDetails d ON d.ID = t.DetailsID
INNER JOIN Products p ON p.ID = d.ProductID
INNER JOIN Users u ON u.UserID= t.UserID
ORDER BY t.CreatedOn DESC

However, that gives me data like this (assuming here that I took off the "top 3"):

ID | ProductName | Amount | FirstName | TransactionID | CreatedOn
1345 | Product XYZ | 6 | Amanda | 319 | 2011-04-04 15:06:15.680
1345 | Product XYZ | 6 | Jane | 318 | 2011-04-04 12:51:53.567
1345 | Product XYZ | 6 | Amanda | 317 | 2011-04-04 11:55:40.203
1349 | Product ABC |  26 | Amanda | 313 | 2011-04-04 10:45:07.793
1382 | Product DEF | 24 | Tom | 311 | 2011-03-31 20:17:19.500
1382 | Product DEF | 22 | George | 314 | 2011-03-31 20:17:19.500
1379 | Product MNO | 50 | Susie | 300 | 2011-03-23 14:12:39.000
1351 | Product LMN | 125 | Tommy | 297| 2011-03-23 14:07:28.277

Open in new window


They're technically "distinct" because the TransationID is unique, but I want to get the top 3 that are DIFFERENT products and DIFFERENT users (I could grab the userID from the Users table above)

So I want to return a result like this (based on the above data)

1345 | Product XYZ | 6 | Amanda | 319 | 2011-04-04 15:06:15.680
1382 | Product DEF | 24 | Tom | 311 | 2011-03-31 20:17:19.500
1379 | Product MNO | 50 | Susie | 300 | 2011-03-23 14:12:39.000

Open in new window


How can I still select all the criteria above (needed for my databinding) but make sure u.Firstname (or u.ID) AND p.ProductID are distinct?
mandi224Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sventhanCommented:
try this

select x.* from
(
SELECT       p.ID
      , p.ProductName
      , t.Amount
      , u.FirstName
      , t.TransactionID
      , t.CreatedOn
      ,rn = row_number() over (partition by p.id,p.ProductName order by p.id )
FROM Transactions t
INNER JOIN TransactionDetails d ON d.ID = t.DetailsID
INNER JOIN Products p ON p.ID = d.ProductID
INNER JOIN Users u ON u.UserID= t.UserID
) x
where x.rn < 4
order by x.createdon desc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mandi224Author Commented:
I'm trying to make it work with your suggestion but it doesn't seem to be helping -- I'm still getting both duplicate Product IDs/Names and duplicate Users
0
mandi224Author Commented:
I was able to do this --

select x.* from
(
SELECT       p.ID
      , p.ProductName
      , t.Amount
      , u.FirstName
      , t.TransactionID
      , t.CreatedOn
      ,rn = row_number() over (partition by p.id order by p.id )
FROM Transactions t
INNER JOIN TransactionDetails d ON d.ID = t.DetailsID
INNER JOIN Products p ON p.ID = d.ProductID
INNER JOIN Users u ON u.UserID= t.UserID
) x
where x.rn = 1
order by x.createdon desc

(I bolded the parts that I changed)

This got me distinct products, but I still ALSO need distinct users, if possible.
0
mandi224Author Commented:
OK wait got it -- added this:

select x.* from
(
SELECT       p.ID
      , p.ProductName
      , t.Amount
      , u.ID As 'UserID'
      , u.FirstName
      , t.TransactionID
      , t.CreatedOn
      ,rn = row_number() over (partition by p.id order by p.id )
      ,rn2 = row_number() over (partion by u.ID order by u.ID)
FROM Transactions t
INNER JOIN TransactionDetails d ON d.ID = t.DetailsID
INNER JOIN Products p ON p.ID = d.ProductID
INNER JOIN Users u ON u.UserID= t.UserID
) x
where x.rn = 1 AND x.rn2 =1
order by x.createdon desc

THAT did the trick. Thank you!
0
mandi224Author Commented:
Had to add a little to the suggested solution to get both fields distinct
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.