Filter results by max value in one field

[brokengod]

Hey guys and gals,

I have been trundling along for a few years, getting around the need to create complex queries by relying on php to provide the logic, but now I can't find a simple solution and wish I had been more creative with mySQL in the past.

I need to request data in a 2 table join.

A.userID | B.source | B.dest| B.level | B.type

And what I want to do is get records where B.type is the maximum out of the records that the query pulls in.

EG> There might be 3 records for a given user, with the same dest, source and type, but the levels are 1, 2 & 3 respectively. How do I limit the record to the row with the highest level without knowing what value it might be?


A.userID | B.source | B.dest | B.level | B.type
-------------------------------------------------
1 UK US 2 mail
1 UK US 1 mail
1 UK US 3* mail

* I only want this record but I don't knwo in advance whether level will be 1, 3 or 5 (or 0)

I ultimately want to stuff this idea into a crosstab, but if I can figure out a way to do the above I will be happy for now.

Thanks in advance,

Brokengod

 

[TonyGroves]

What is the structure of your tables?

-----
ALTER world DROP injustice, ADD peace;

 

[KarveR]

SELECT A.userID, B.source, B.dest, B.level, B.type FROM table1 A, table2 B where A.someitem=B.someitem order by B.dest limit 1;

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[brokengod]

Table 1 - users

Fields: id , surname, givenname, ph, fax, email etc

Table 2 - skills

Fields - id, memberID, source, dest, level, type

where memberID is the key to table 1

 

[brokengod]

i should also mention that each userID might be associated with different combinations of source and dest:

eg
A.userID | B.source | B.dest | B.level | B.type
-------------------------------------------------
1 UK US 2 mail
1 UK US 1 mail
1 UK US 3* mail
1 UK AUS 1 mail
1 UK AUS 3* mail
1 AUS US 3* mail
1 UK CHN 3* mail

* These are the desired records because for each combination of source and dest for each user, they are the max levels...