retrieving most instances of a record

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

SQL Server 2008 Forums

Transact-SQL (2008)

retrieving most instances of a record

Author

Topic

studmuffin
Starting Member

4 Posts

Posted - 2010-05-04 : 22:36:38

So I have the following table and information

CREATE TABLE #tmp(
[GR] [varchar](50) NULL,
[SE] [varchar](50) NULL,
[AM_CODE] [varchar](50) NULL,
[ROW_ID] [int] NULL)

INSERT into #tmp
select '12951','001','0110',1 UNION ALL
select '12951','001','0110',2 UNION ALL
select '12951','001','9506',3 UNION ALL
select '12951','001','9506',4 UNION ALL
select '12951','001','9506',5 UNION ALL
select '12951','001','9506',6 UNION ALL
select '12951','002','8000',7 UNION ALL
select '12951','002','8000',8 UNION ALL
select '12951','002','7000',9 UNION ALL
select '10000','001','0555',10

What I am trying to do is write a query that returns AM_CODE based on the most occurrences for each GR and SE value, but I am having some problems, Here is what I would like the output to look like:

GR SE AM_CODE
12951 001 9506
12951 002 8000
10000 001 0555

the 9506 showed up 4 times as opposed to the 0110 which showed up only twice, so the 9506 should be returned based on the GR = 12951 and the SE=001, the same applies to the other records as well.

Any help would be greatly appreciated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-04 : 23:17:05

"query that returns AM_CODE based on the most occurrences for each GR and SE value"
what do you mean by that ? can you explain how do you get the required result ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 02:47:45

[code]
;With CTE AS
(
SELECT DISTINCT GR, SE, AM_CODE,COUNT(1) OVER (PARTITION BY GR, SE, AM_CODE) AS Occurance
FROM Table
)

SELECT GR, SE, AM_CODE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GR, SE, AM_CODE ORDER BY Occurance DESC) AS Seq,GR, SE, AM_CODE
FROM CTE
)t
WHERE Seq=1
[/code]

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

studmuffin
Starting Member

4 Posts

Posted - 2010-05-05 : 06:13:31

"query that returns AM_CODE based on the most occurrences for each GR and SE value"
what do you mean by that ? can you explain how do you get the required result ?

For instance, when GR = 12915, and SE = 001, AM_CODE can equal either 0110 or 9506,, I want to write a query that will return the one that has the most AM_CODE's based on SE and GR. So in this case it should return a record that has GR = 12951, SE = 001, and AM_CODE = 9506, because there are 4 instances of the 9506, and only two of the 0110.

studmuffin
Starting Member

4 Posts

Posted - 2010-05-05 : 06:22:35

quote:
Originally posted by visakh16

;With CTE AS
(
SELECT DISTINCT GR, SE, AM_CODE,COUNT(1) OVER (PARTITION BY GR, SE, AM_CODE) AS Occurance
FROM Table
)

SELECT GR, SE, AM_CODE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GR, SE, AM_CODE ORDER BY Occurance DESC) AS Seq,GR, SE, AM_CODE
FROM CTE
)t
WHERE Seq=1

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

Thanks, but this returns too many records as I am only looking for the most rows that have the same GR, and SE. For instance when GR = 12951 and SE = 001 two records exist for AM_CODE of 0110 and 4 records exist for AM_CODE 9506. I am looking for the one that has the most rows, so I am trying to return just the row that has 9506 and dis-regarding the one that has 0110, and so on for the rest of the table.

10000 001 0555
12951 001 0110 (This row should not appear)
12951 001 9506
12951 002 7000 (This row should not appear)
12951 002 8000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-05-05 : 06:30:03

[code]
select GR, SE, AM_CODE
from
(
select GR, SE, AM_CODE, row_no = row_number() over (partition by GR, SE order by count(*) desc)
from #tmp
group by GR, SE, AM_CODE
) t
where row_no = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-05 : 07:26:07

quote:
Originally posted by studmuffin

quote:
Originally posted by visakh16

;With CTE AS
(
SELECT DISTINCT GR, SE, AM_CODE,COUNT(1) OVER (PARTITION BY GR, SE, AM_CODE) AS Occurance
FROM Table
)

SELECT GR, SE, AM_CODE
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY GR, SE~~, AM_CODE~~ ORDER BY Occurance DESC) AS Seq,GR, SE, AM_CODE
FROM CTE
)t
WHERE Seq=1

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

Thanks, but this returns too many records as I am only looking for the most rows that have the same GR, and SE. For instance when GR = 12951 and SE = 001 two records exist for AM_CODE of 0110 and 4 records exist for AM_CODE 9506. I am looking for the one that has the most rows, so I am trying to return just the row that has 9506 and dis-regarding the one that has 0110, and so on for the rest of the table.

10000 001 0555
12951 001 0110 (This row should not appear)
12951 001 9506
12951 002 7000 (This row should not appear)
12951 002 8000

try the modification above

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

studmuffin
Starting Member

4 Posts

Posted - 2010-05-05 : 20:01:56

Thanks visakh16

That is exactly what I was looking for....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-06 : 13:45:24

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources