Order by clause help.

[blaketar]

I have one main table (JOBS) which contains a price field (JOBS.PRICE). Some of the jobs in this table however can have adjusted bid prices on them, if the job is available for bids, these prices are then held in a separate table named (JOBBIDS). A single job may have multiple bids; I am only interested in sorting by the highest value in (JOBBIDS.CURRENT_BID).
How would I go about sorting the job list by JOBS.PRICE and also (JOBBIDS.CURRENT_BID = MAX CURRENT_BID).
Example:

TABLE: JOBS
ID ¦¦ OPENTOBIDS ¦¦ JOB ¦¦ PRICE
3233 ¦¦ NULL ¦¦Rake the yard ¦¦ $46.00
9948 ¦¦ NULL ¦¦Weed flowerbeds ¦¦ $37.00
0039 ¦¦ NULL ¦¦Trim Branches ¦¦ $31.50
4332 ¦¦ 1 ¦¦Clean Gutters ¦¦
3944 ¦¦ NULL ¦¦Wash Car ¦¦ $14.00
8433 ¦¦ 1 ¦¦Mow Lawn ¦¦
9993 ¦¦ 1 ¦¦Sweep ¦¦

TABLE: JOBBIDS
ID ¦¦ JOBID ¦¦ CURRENT_BID
2 ¦¦ 4332 ¦¦ $27.50
3 ¦¦ 4332 ¦¦ $30.50
4 ¦¦ 4332 ¦¦ $24.30
5 ¦¦ 8433 ¦¦ $12.00
6 ¦¦ 9993 ¦¦ $6.00
7 ¦¦ 8433 ¦¦ $16.50

DISPLAY:
Rake the yard - $46.00
Weed flowerbeds - $37.00
Trim Branches - $31.50
Clean Gutters - $30.50
Wash Car - $14.00
Mow Lawn - $16.50
Sweep - $6.00

[coopster]

First off, I hope that your PRICE column is not a string, meaning you should not be storing the dollar sign in there. That is going to make your work much more difficult. Of what column type have you defined PRICE?

[blaketar]

Both column type of PRICE and CURRENT_BID are type MONEY. DB is Postgresql

[coopster]

You could write the query a number of ways, two off the top of my head are ...

SELECT JOB, MAX(PRICE) AS PRICE FROM (SELECT ID, JOB, PRICE FROM JOBS UNION SELECT JOBBIDS.JOBID AS ID, JOBS.JOB AS JOB, CURRENT_BID AS PRICE FROM JOBBIDS INNER JOIN JOBS ON (JOBBIDS.JOBID = JOBS.ID)) AS groupedJobs GROUP BY JOB ORDER BY PRICE DESC;

SELECT JOBS.JOB, MAX(groupedJobs.PRICE) AS PRICE FROM JOBS INNER JOIN (SELECT J.ID, J.PRICE FROM JOBS AS J UNION SELECT JOBBIDS.JOBID, CURRENT_BID FROM JOBBIDS) AS groupedJobs ON(JOBS.ID = groupedJobs.ID) GROUP BY JOBS.JOB ORDER BY PRICE DESC;

Either query produces the correct results but something about this just isn't right to me. It may be the relative table structure (design) or it may be the way I threw the queries together. Joining the data by ID is key but perhaps a logic structure such as CASE/IF to check for NULL 'OPENTOBIDS' may be something to look into. It should get you started in the right direction anyway. Maybe somebody else with a bit more time than I have right now can chime in ...