Display most popular products only.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Display most popular products only.

Author

Topic

MartynOgea
Starting Member

4 Posts

Posted - 2012-12-02 : 16:55:53

Hi everyone, I need help in finding out how to display only the most frequent product for each branch.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-02 : 19:20:18

[code]
DECLARE @T TABLE(BranchID INT,MostProducts VARCHAR(100))

INSERT INTO @T VALUES (1,'trainers')
INSERT INTO @T VALUES (1,'trainers')
INSERT INTO @T VALUES (1,'jumpers')
INSERT INTO @T VALUES (2,'tie')
INSERT INTO @T VALUES (2,'shoes')
INSERT INTO @T VALUES (2,'tie')
INSERT INTO @T VALUES (2,'shoes')

;WITH CTE
AS
(
SELECT distinct
COUNT(MostProducts) OVER(PARTITION BY BranchID,MostProducts) AS Seq,
BranchId,
MostProducts
FROM
@T
)
, CTE1
AS
(
SELECT *,DENSE_RANK() OVER(PARTITION BY BranchID ORDER BY CTE.Seq DESC) AS Seq1
FROM CTE
)

SELECT
CTE1.BranchID,
CTE1.MostProducts,
CTE1.Seq
FROM
CTE1
WHERE
CTE1.Seq1 = 1[/code]

MartynOgea
Starting Member

4 Posts

Posted - 2012-12-02 : 23:29:10

Hi there, thanks for the reply.

But I'm don't quite understand what you did. Is there a way just to do it with a query without the insert and, some keywords not recognised in HeidiSQL such as OVER and Dense_rank(). Also what is @T?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-02 : 23:50:07

Yes we do without the function. What Version of SQL you are using?
Just replace @T with your table. Insert was just an example.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-12-03 : 08:04:11

[code]
SELECT BranchNo,Products
FROM (SELECT DISTINCT BranchNo FROM table)t
CROSS APPLY (SELECT TOP 1 WITH TIES Products
FROM table
WHERE BranchNo = t.BranchNo
GROUP BY Products
ORDER BY COUNT(*) DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-04 : 15:10:32

quote:
Originally posted by MartynOgea

Hi everyone, I need help in finding out how to display only the most frequent product for each branch.

What is purpose of deleting the content after you get solution?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources