SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Display most popular products only.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MartynOgea
Starting Member

4 Posts

Posted - 12/02/2012 :  16:55:53  Show Profile  Reply with Quote
Hi everyone, I need help in finding out how to display only the most frequent product for each branch.

Edited by - MartynOgea on 12/04/2012 15:03:54

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  19:20:18  Show Profile  Reply with Quote

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

Edited by - sodeep on 12/02/2012 19:21:14
Go to Top of Page

MartynOgea
Starting Member

4 Posts

Posted - 12/02/2012 :  23:29:10  Show Profile  Reply with Quote
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?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/02/2012 :  23:50:07  Show Profile  Reply with Quote
Yes we do without the function. What Version of SQL you are using?
Just replace @T with your table. Insert was just an example.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/03/2012 :  08:04:11  Show Profile  Reply with Quote

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


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

Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/04/2012 :  15:10:32  Show Profile  Reply with Quote
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?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000