filtering on a join - 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

SQL Server 2000 Forums

Transact-SQL (2000)

filtering on a join

Author

Topic

cornall
Posting Yak Master

148 Posts

Posted - 2007-04-20 : 07:40:20

I have simplified this as much as i can

I am trying to join two tables A and B

Table A has fields IDA1
TABLE B has fields IDA2 the forign key to table a and IDB1

SELECT * FROM A
Left Join B On A.IDA1 = B.IDA2

Let say this returns 6 rows :-

All I want is the rows where IDB1 is the maximum :-


IDA1 IDB1
 1    4
 2    6

How do I do this? I just can't think today! Sure this is quite simple.

Cheers in advance.

cornall
Posting Yak Master

148 Posts

Posted - 2007-04-20 : 07:58:34

I think this is my answer? Any better way to do it?

SELECT * FROM A
Left Join B On A.IDA1 = B.IDA2
AND B.IDB1 = (SELECT MAX(B.IDB1) FROM B WHERE A.IDA1 = B.IDA2)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-20 : 08:02:41

Simple GROUP BY with MAX().

Select A.IDA1, Max(B.IDA2) as IDA2
from A Left Join B On A.IDA1 = B.IDA2
GROUP BY A.IDA1

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

cornall
Posting Yak Master

148 Posts

Posted - 2007-04-20 : 08:47:13

The actual table contains other fields that don't work with Group by and give the error "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

This is a better example of what I want


IDA1 IDB1 NOTE
 1    1   aaaa
 1    2   bbbb
 1    3   cccc
 1    4   dddd
 2    5   eeee
 2    6   ffff


IDA1 IDB1 NOTE
 1    4   dddd
 2    6   ffff

cornall
Posting Yak Master

148 Posts

Posted - 2007-04-20 : 09:00:36

Do I need to use convert(varchar(50),NOTE) ??

kimharlan
Starting Member

9 Posts

Posted - 2007-04-20 : 09:14:11

Every field that you are selecting needs to be included in a GROUP BY clause, when you use a function like MAX. So if you are selecting a, b and c, you need to "GROUP BY b, c, a" (or in whatever order you want them). I hope that helps. :-)

cornall
Posting Yak Master

148 Posts

Posted - 2007-04-20 : 09:27:39

I understand that all the fields need to be included in my GROUP BY. I have around 27 fields and most of them are of type text so to include them in my GROUP BY do I need to use convert(varchar(x),field_name).

This also worked and doesn't need GROUP BY but is probably more inefficient as it has a sub select? :-

SELECT * FROM A
Left Join B On A.IDA1 = B.IDA2
AND B.IDB1 = (SELECT MAX(B.IDB1) FROM B WHERE A.IDA1 = B.IDA2)

sshelper
Posting Yak Master

216 Posts

Posted - 2007-04-20 : 09:32:01

Try this query:

SELECT *
FROM A INNER JOIN B ON A.IDA1 = B.IDA2
INNER JOIN (SELECT IDA1, MAX(IDB1) AS IDB1
FROM A INNER JOIN B ON A.IDA1 = B.IDA2) C
ON A.IDA1 = C.IDA1 AND B.IDB1 = C.IDB1

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources