homepage Welcome to WebmasterWorld Guest from 54.161.166.171
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Order by clause help.
blaketar




msg:3739317
 6:57 pm on Sep 5, 2008 (gmt 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

 

coopster




msg:3739454
 11:08 pm on Sep 5, 2008 (gmt 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?

blaketar




msg:3739466
 11:21 pm on Sep 5, 2008 (gmt 0)

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

coopster




msg:3740871
 10:18 pm on Sep 8, 2008 (gmt 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 ...

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved