I have a question that ought to be simple but has given me headaches for a while.
I have a table with contact email addresses, say.
Expand|Select|Wrap|Line Numbers
- T
- ID ContID Priority Emailaddress
- ----------------------------------------------
- 1 1 10 email1@contact1.fi
- 2 1 7 email2@contact1.fi
- 3 2 9 email1@contact2.fi
- 4 2 8 ....
- ...
Now, the main question:
I want to create a query that gives me
- the ID-number of the records
- that correspond to the highest-priority emailaddress
- of each contact.
I then want to use this query-result of ID-numbers to INNER JOIN to table T (or actually a table with more fields) to get the fields I'm interested in; the joining being done on the ID-fields, something like:
Expand|Select|Wrap|Line Numbers
- SELECT t1.ContID, t1.Emailaddress
- FROM t AS t1
- INNER JOIN
- (
- SELECT "the ID with the highest priority"
- FROM t AS t2
- GROUP BY t2.ContID
- ) AS t3 ON t3.ID = t1.ID
(It IS possible to "get the maximum ID of the records for each contact" or "get the maximum priority for each contact", as shown below.)
I did think of some solutions, but none that I like.
I could do the JOINing on other fields, something like
Expand|Select|Wrap|Line Numbers
- SELECT t1.ContID, t1.Emailaddress
- FROM t AS t1
- INNER JOIN
- (
- SELECT t2.ContID, MAX(t2.priority) AS MaxPrio
- FROM t AS t2
- GROUP BY t2.ContID
- ) AS t3 ON t3.ContID = t1.ContID AND t3.MaxPrio = t1.Priority
This could multiplicity can be solved by GROUPing also in the outer query, like this:
Expand|Select|Wrap|Line Numbers
- SELECT t1.ContID, FIRST(t1.Emailaddress) AS FirstEmail
- FROM t AS t1
- INNER JOIN
- (
- SELECT t2.ContID, MAX(t2.priority) AS MaxPrio
- FROM t AS t2
- GROUP BY t2.ContID
- ) AS t3 ON t3.ContID = t1.ContID AND t3.MaxPrio = t1.Priority
- GROUP BY t1.ContID
It also makes it difficult to include additional conditions: if the table has a yes/no field 'IsWork', and I want to get the highest-priority work-email, I need to include the 'WHERE IsWork = TRUE' line in both the inner and the outer query.
It's just not pretty!
Another possible solution is
Expand|Select|Wrap|Line Numbers
- SELECT t1.contID, t1.EmailAddress
- FROM t AS t1
- INNER JOIN
- (
- SELECT t2.ID, COUNT(*)
- FROM t AS t2
- INNER JOIN t AS t3 ON (t2.ContID = t3.ContID) AND ((t2.priority < t3.priority) OR (t2.priority = t3.priority AND t2.ID < t3.ID))
- GROUP BY t2.ID
- HAVING COUNT(*) =1
- ) AS t4 ON t4.ID =t4.ID
- ORDER BY t1.contID
I like this solution; all the selecting of the correct ID numbers is done isolatedly in the inner query, and additional conditions can be included here without needing to change something in the outer query.
But it's really slow! :(
Can someone help me out? Suggestions for an adequete, pretty, fast query to do the job?
Thanks!!
Sjaak
if wanted i can prepare an Access file, of course.
I'm using access 2007, SQL ANSI-92