SQL query problem - SQL Server Forums

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

SQL query problem

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-09-30 : 21:28:41

I have following sample data:

S_id t_Id NoOfCount
--------------------------
QAW 1020 45
CTS 1020 5
SSI 34 12
TCI 34 36
MCI 34 50
..............................
I need following data based on the maximum NoOfCount for t_id. How can I write the query?

S_id t_Id NoOfCount
--------------------------
QAW 1020 45
MCI 34 50
..........................
Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-30 : 23:12:25

are you using SQL 2005 / 2008 ?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:14:12

[code]SELECT t.*
FROM table t
INNER JOIN (SELECT S_id ,t_Id,MAX(NoOfCount) AS Occurance
FROM table
GROUP BY S_id ,t_Id)t1
ON t.S_id =t1.S_id
AND t.t_Id=t1.t_Id
AND t.NoOfCount=t1.Occurance
[/code]

sanjnep
Posting Yak Master

191 Posts

Posted - 2009-10-01 : 12:54:53

I am using SQL Server 2008
Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-01 : 13:18:42

What if there are ties?


CREATE TABLE #myTable99 (
	  S_id		varchar(10)
	, t_Id		int
	, NoOfCount	int
)
GO

INSERT INTO #myTable99(S_id,t_Id, NoOfCount)
SELECT 'QAW', 1020, 45 UNION ALL
SELECT 'CTS', 1020, 5  UNION ALL
SELECT 'BJK', 1020, 45 UNION ALL
SELECT 'SSI', 34,   12 UNION ALL
SELECT 'TCI', 34,   36 UNION ALL
SELECT 'MCI', 34,   50 
GO

    SELECT l.S_id, l.t_Id, l.NoOfCount
      FROM #myTable99 l
INNER JOIN (
			  SELECT t_Id, MAX(NoOfCount) AS MAX_NoOfCount
				FROM #myTable99
			GROUP BY t_Id) AS r
		ON l.t_Id		= r.t_Id
	   AND l.NoOfCount	= r.MAX_NoOfCount
GO

DROP TABLE #myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-01 : 13:19:45

quote:

Originally posted by visakh16

SELECT t.*
FROM table t
INNER JOIN (SELECT S_id ,t_Id,MAX(NoOfCount) AS Occurance
            FROM table
            GROUP BY S_id ,t_Id)t1
ON t.S_id =t1.S_id 
AND t.t_Id=t1.t_Id
AND t.NoOfCount=t1.Occurance

They only want the MAX of t_Id

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources