Combining LEFT JOIN and MAX

i've got two tables:

table: PEOPLE
columns: ID, NAME

table: TESTS
columns: ID, TEST_NUM, RESULT

rules:
1. one person can have from zero to many tests.
2. i want to see one row for each person, regardless of whether they have any tests or not.
3. i only want to see the most recent (highest numbered) test.

SQL:
i'm able to return all the rows using a LEFT JOIN, but i still need a way to only return the most recent test.

SELECT A.ID, A.NAME,
B.TEST_NUM, B.RESULT
FROM PEOPLE A
LEFT JOIN TESTS B
ON A.ID = B.ID

here's what's in the tables:

PEOPLE:
ID NAME
-- --
1 JOE
2 BOB
3 JIM
4 JOHN
5 RALPH
6 STEVE
7 MARY
8 BETH
9 SUE
10 KIM

TESTS:
ID TEST_NUM RESULT
-- --- --
1 1 PASS
1 2 PASS
2 1 FAIL
3 1 FAIL
4 1 PASS
4 2 FAIL
4 3 FAIL
6 1 FAIL
8 1 PASS
9 1 PASS

here's what the query currently returns:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 1 PASS
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 1 PASS
4 JOHN 2 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIM

here's what i want it to return:

ID NAME TEST_NUM RESULT
-- -- --- --
1 JOE 2 PASS
2 BOB 1 FAIL
3 JIM 1 FAIL
4 JOHN 3 FAIL
5 RALPH
6 STEVE 1 FAIL
7 MARY
8 BETH 1 PASS
9 SUE 1 PASS
10 KIMtry this --select a.id
, a.name
, b.test_num
, b.result
from people a
left
join tests b
on a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||i get a syntax error with that.

it doesn't tell me why, maybe it doesn't like the 'and' in the 'join'?|||what is the syntax error?

oh, and perhaps you could also let us know which database you're using|||the real database will be DB2, but i'm testing on a mock access db for the time being. i get the same results in each.

ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ''.

i only got this error after i added this:

and b.test_num
= (select max(test_num)
from tests
where id = a.id)|||try this --

on (
a.id = b.id
and b.test_num
= (select max(test_num)
from tests
where id = a.id)
)
you realize that sql is never portable between different databases, right?|||it's been my experience that basic SQL is portable. it's just the more complicated functions that aren't.

DB2 Error:

ODBC Error Code = 37000 (Syntax error or access violation)
[StarQuest][StarSQL ODBC Driver]SQL syntax error, .

Access Error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression ' a.id = b.id and b.test_num = (select max(test_num) from tests where id = a.id) '.

it doesn't seem to like those outer parenthesis.|||select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, null
, null
from people a
left
join tests b
on a.id = b.id
and b.id is null|||DB2 doesn't seem to like the nulls.

access had a problem with the join, but who cares about access?

ODBC Error Code = 42703 ()

[StarQuest][StarSQL ODBC Driver][DB2]NULL IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE|||okay, for the second subselect, try:select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from ...|||oops, change the and in the last line to where|||hey, i think that worked.

there's no error at least.

some of my test data got purged overnight, so i'm going to repopulate the tables and give it a shot.

i'll post back when i do.

akosz

ps - don't bother with that devshed forum. i cross posted when this first started to see if anybody anywhere had any answers. apparently you do :)|||well that did the trick!

thanks a million for getting me through that one. :)

the bad news is, it looks like we're allowing duplicate id's in the PEOPLE table. a timestamp will differentiate between them and allow for a primary key. i really don't know how they want me to accomodate for this as it relates to the application i'm developing.

it's going to be a long afternoon...

akosz

btw - there are actually quite a few more columns and tables than what you see here, i just scaled it down to get at the syntax.|||now i need to add a WHERE (or an AND) clause to this so that i only get certain matches in the PEOPLE table.

here's the query:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id)
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null

here's what i need to add:

WHERE a.id IN (123,999,343)

i tried adding this as an AND clause after the first WHERE but it only brough back one row instead of 3.|||nevermind, i just needed some coffee :)

it's:

select a.id
, a.name
, b.test_num
, b.result
from people a
inner
join tests b
on a.id = b.id
where b.test_num
= (select max(test_num)
from tests
where id = a.id
and a.id IN (123,999,343))
union all
select a.id
, a.name
, cast(null as integer) as test_num
, cast(null as char(4)) as result
from people a
left
join tests b
on a.id = b.id
where b.id is null

Column max size for String

Hello I currently am using the nvarchar type which has a maximum size of 4000 char's. Is there any other type that I can use which can store a greater amount of char's ?

Many thanks

GrantVarchar can go up to 8000. You can usetext orntext fields which have no practical upper limit on the number of chars. Withtext orntext fields fields you lose some flexibility such as the ability to use thetext orntext field in a where clause.

text is for variable-length non-Unicode data.

ntext is for variable-length Unicode data.|||text datatypes can hold upto 2GB bytes of data ... And ntext also used the same limitation ... The only difference being text datatypes use 1 bytes for 1 character and ntext types use 2 bytes for storing single unicode character ...

Column count max for Merge replication still 246?

Is the maximum number columns per table still 246 for a Merge replication in SQL Server 2005?

yes its still 246 coulumns in SQL Server 2005 Merge replication

see this link

http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Madhu

|||

Madhu,

Can I replicate a table more than 264 columns in Transactional replication.?

During my initial setup, the publisher did not allow me to publisher a table with more than 246 columns.?

Thank you.

Edwin

|||Transactional allows upto 1024 columns.