Problem Queries

As a way of focusing on both specific SQL techniques and keeping one's eyes open for alternatives, we look at a standard SQL problem and three possible resolutions:

How do I find the latest status for each job and to whom it is assigned?

You cannot use a standard group by statement if you want to see to whom the job is assigned. The rows we want are highlighted.

jobid	jobstatus	statusdate	assignedto
1	1	Dec 2 2003 1:58PM	B
1	2	Dec 2 2003 1:58PM	A
1	3	Dec 2 2003 1:58PM	B
1	4	Dec 2 2003 1:58PM	E
1	5	Dec 2 2003 1:58PM	C
2	1	Dec 2 2003 1:58PM	B
2	2	Dec 2 2003 1:58PM	E
2	3	Dec 2 2003 9:56PM	C
2	4	Dec 2 2003 10:01PM	C
3	1	Dec 2 2003 1:58PM	D
3	1	Dec 2 2003 1:58PM	E
3	3	Dec 2 2003 2:28PM	A
3	3	Dec 2 2003 2:29PM	E
3	4	Dec 2 2003 2:29PM	E
3	2	Dec 4 2003 11:59AM	A
4	1	Dec 2 2003 1:58PM	B
4	2	Dec 2 2003 1:58PM	E
4	5	Dec 2 2003 1:58PM	B
4	3	Dec 2 2003 2:13PM	C
4	2	Dec 4 2003 12:55PM	C
5	1	Dec 2 2003 1:58PM	A
5	2	Dec 2 2003 1:58PM	A
5	5	Dec 2 2003 2:14PM	C
5	5	Dec 2 2003 2:30PM	D
6	1	Dec 2 2003 2:38PM	A
6	2	Dec 2 2003 3:36PM	D
7	1	Dec 2 2003 3:21PM	A
7	2	Dec 3 2003 1:29PM	A
7	3	Dec 3 2003 1:30PM	A
8	1	Dec 3 2003 1:31PM	D
8	2	Dec 3 2003 1:34PM	D
8	2	Dec 3 2003 1:35PM	A
8	3	Dec 3 2003 1:35PM	A
8	4	Dec 3 2003 1:35PM	B

Three possible solutions

The problem is how to get the max, min, latest item from a list with many candidates.

Follow the links to see the relative merits of each solution.

Location Map

Updates

SQL Problems

Problem Queries

How do I find the latest status for each job and to whom it is assigned?

Three possible solutions

Copyright DBU 2000. Last updated 24 May 2004

Copyright DBU 2000.
Last updated 24 May 2004