CODEWITHSUNDEEP
sqlsql-server
izip
izip

Reputation: 2096

SQL Group By Question

I have a table which tracks views of products.

TrackId ProductId CreatedOn
1       1         01/01/2011
2       4         01/01/2011
3       4         01/01/2011
4       10        01/01/2011

What I want to do is return a dataset which doesn't have two ProductIds next to each other. I.E from the above data set I would want to return:

TrackId ProductId CreatedOn
1       1         01/01/2011
2       4         01/01/2011
4       10        01/01/2011

I can't use distinct as far as I am aware as this is row based?

Help appreciated.

Upvotes: 0

Views: 70

Answers (3)

gbn
gbn

Reputation: 432431

Generate a row number sequence per ProductID, take the first

;WITH cte AS
(
    SELECT
       *,
       ROW_NUMBER() OVER (PARTITION BY ProductID ORDER BY TrackID) AS rn
    FROM
       MyProductTable
)
SELECT
   TrackId ProductId CreatedOn
FROM
   cte
WHERE
   rn = 1

Edit:

If you want to use an aggregate, you need a separate subquery first to ensure consistent results. A straight MIN won't work.

This is based on my comment to the question

"not having productid in two adjacent rows. Adjacent is defined by next/previous Trackid"

SELECT
    M.*
FROM
    myProductTable M
    JOIN
    ( --gets the lowest TrackID for a ProductID
    SELECT ProductID, MIN(TrackID) AS MinTrackID
    FROM myProductTable
    GROUP BY ProductID
    ) M2 ON M.ProductID= M2.ProductID AND M.TrackID= M2.MinTrackID

Upvotes: 6

Phil Murray
Phil Murray

Reputation: 6554

You can GroupBy on the TrackID and ProductID and do a Min of the CreatedOn if the date is not important.

SELECT TrackID ,ProductID ,MIN(CreatedOn)
FROM [table]
GROUP BY TrackID ,ProductID

If the date is the same you can group by all three

SELECT TrackID ,ProductID ,CreatedOn
FROM [table]
GROUP BY TrackID ,ProductID ,CreatedOn

Upvotes: 0

UltraCommit
UltraCommit

Reputation: 2276

select min(TrackId), ProductId, CreatedOn
from YourTable
group by ProductId, CreatedOn;

Upvotes: 0

Related Questions