7bdkGN6t

11. I have two tables 
2
3CREATE TABLE IF NOT EXISTS `jobs` (
4  `id` int(11) NOT NULL AUTO_INCREMENT,
5  `name` varchar(255) NOT NULL,
6  `description` mediumtext NOT NULL,
7  `compensation` varchar(50) DEFAULT NULL,
8  `preferred_location` tinyint(1) DEFAULT NULL,
9  `zip_code_id` int(11) DEFAULT NULL,
10  `city_id` int(11) DEFAULT NULL,
11  `state_id` int(11) DEFAULT NULL,
12  `user_id` int(11) NOT NULL,
13  `draft` tinyint(1) DEFAULT NULL,
14  `posted` tinyint(1) DEFAULT NULL,
15  `posted_date` datetime DEFAULT NULL,
16  `hiring` tinyint(1) DEFAULT NULL,
17  `completed` tinyint(1) DEFAULT NULL,
18  `cancel` tinyint(1) DEFAULT NULL,
19  `proposal_count` int(11) DEFAULT NULL,
20  `created` datetime DEFAULT NULL,
21  `modified` datetime DEFAULT NULL,
22  PRIMARY KEY (`id`)
23) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
24
25CREATE TABLE IF NOT EXISTS `proposals` (
26  `id` int(11) NOT NULL AUTO_INCREMENT,
27  `description` text NOT NULL,
28  `price` int(11) NOT NULL,
29  `timeframe` date NOT NULL,
30  `job_id` int(11) NOT NULL,
31  `user_id` int(11) NOT NULL,
32  `created` datetime DEFAULT NULL,
33  `modified` datetime DEFAULT NULL,
34  PRIMARY KEY (`id`)
35) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;
36
372. query used 
38
39SELECT 
40`Job`.`id`, `Job`.`name`, `Job`.`hiring`, `Job`.`completed`, `Job`.`cancel`, `Job`.`proposal_count`, `Job`.`created`, `Job`.`modified` 
41FROM `jobs` AS `Job`
42WHERE `Job`.`hiring` IS NULL 
43AND   `Job`.`completed` IS NULL 
44AND   `Job`.`cancel` IS NULL 
45ORDER BY `Job`.`created` DESC, `Job`.`modified` DESC 
46LIMIT 10
47
483. Query Result 
49+----+-----------------------------------------------------------------+--------+-----------+--------+----------------+---------------------+---------------------+
50| id | name                                                            | hiring | completed | cancel | proposal_count | created             | modified            |
51+----+-----------------------------------------------------------------+--------+-----------+--------+----------------+---------------------+---------------------+
52|  4 | Paypal Integration Required                                     |   NULL |      NULL |   NULL |           NULL | 2012-07-02 16:43:40 | 2012-07-02 16:43:40 |
53|  3 | Google Shopping Setup and FaceBook Setup with my volusion Site. |   NULL |      NULL |   NULL |              1 | 2012-07-01 20:31:38 | 2012-07-02 15:10:11 |
54|  2 | Logo Design                                                     |   NULL |      NULL |   NULL |              1 | 2012-07-01 20:29:57 | 2012-07-02 16:49:47 |
55|  1 | Need Facebook Integration for Website                           |   NULL |      NULL |   NULL |              3 | 2012-07-01 20:28:31 | 2012-07-02 16:37:03 |
56+----+-----------------------------------------------------------------+--------+-----------+--------+----------------+---------------------+---------------------+
574 rows in set (0.00 sec)
58
594. My client has the following requirement
60
61	a) I think you misinterpreted the statement. A new job without any proposal is still a new job.
62
63	b) Jobs that are just posted should appear higher in search results than jobs posted before it. 
64	   However, each time a new proposal is submitted on a job, the job should move to the top of the list just like a newly posted job does. 
65	   Then when another job receives a new proposal, that one moves higher, and so on.