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?
 
sventhanConnect With a Mentor Commented:
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
 
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
 
mandi224Connect With a Mentor Author 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.