Welcome to WebmasterWorld Guest from 54.196.214.35

Forum Moderators: open

Message Too Old, No Replies

Order by clause help.

     
6:57 pm on Sep 5, 2008 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts: 202
votes: 0


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

11:08 pm on Sept 5, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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?
11:21 pm on Sept 5, 2008 (gmt 0)

Full Member

10+ Year Member

joined:June 24, 2004
posts:202
votes: 0


Both column type of PRICE and CURRENT_BID are type MONEY. DB is Postgresql
10:18 pm on Sept 8, 2008 (gmt 0)

Administrator

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:July 31, 2003
posts:12533
votes: 0


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 ...