Help in writing SQL query - 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

Help in writing SQL query

Author

Topic

frmpk
Starting Member

2 Posts

Posted - 2012-05-06 : 16:23:11

Following are two tables T1 and T2 with primary keys PK each. T1 and T2 are have a relation through FK (PK of T1 in T2).

I want to write a SQL query to select T1.* (all columns from T1) and for each unique FK find maximum digit1 and related digit2.

For example:

For T1.PK=1 the maximum digit1 in T2 is '4' and corrosponding digit2 is '3' (solution row marked as *)

similarly, for T1.PK=2 the maximum digit1 in T2 is 6 and corrosponding digit2 is 7 (solution row marked as **)

T1: PK col1 col2
1 a b
2 c d
3 e f
4 g h
5 i j

T2: PK FK digit1 digit2
1 1 0 1
2 1 2 4
* 3 1 4 3
** 4 2 6 0
5 2 1 7
6 4 4 2

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 16:27:42

[code]
SELECT t1.*,t2.digit2
FROM T1 t1
INNER JOIN T2 t2
ON t2.FK = t1.PK
INNER JOIN (SELECT FK,MAX(digit1) AS digit1
FROM T2
GROUP BY FK)t21
ON t21.FK = t2.FK
AND t21.digit1 = t2.digit1
[/code]

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

frmpk
Starting Member

2 Posts

Posted - 2012-05-06 : 17:07:51

Thanks you very much for the QUICK and CORRECT response.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-06 : 17:08:57

welcome

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources