1

I want to sort the result first by date and then by id.

I used the query

SELECT payment_date,project_id FROM `payments` ORDER BY payment_date desc, project_id desc

But it did not work. I want first sort the data by date and get the project id who have max date after that records below first row show all the date based on particular project id.

like

enter image description here

Please help me to get the result as below:

enter image description here

Here is the Data table http://sqlfiddle.com/#!9/f2358/5

DhirendraBisht
  • 110
  • 1
  • 10
  • Possible duplicate of [PHP MySQL Order by Two Columns](http://stackoverflow.com/questions/514943/php-mysql-order-by-two-columns) – Samrat Das May 26 '16 at 05:29
  • Then you want to order by the id first and then by the date. – Shadow May 26 '16 at 05:48
  • I already used SELECT payment_date,project_id FROM `payments` ORDER BY project_id desc,payment_date desc . payment_date project_id 2016-07-25 517 2016-07-15 517 2016-07-08 517 .. .. 2016-07-07 516 2016-07-30 516 .. .. 2016-07-01 515 2016-07-24 515 – DhirendraBisht May 26 '16 at 05:52
  • Isn't that the result you're looking for? – groovenectar May 26 '16 at 05:54

2 Answers2

0

I think that @Shadow is correct. Try this:

SELECT payment_date, project_id FROM `payments` ORDER BY project_id DESC, payment_date DESC
groovenectar
  • 2,828
  • 3
  • 22
  • 26
  • it show results payment_date project_id 2016-07-25 517 2016-07-15 517 2016-07-08 517 .. .. 2016-07-07 516 2016-07-30 516 .. .. 2016-07-01 515 2016-07-24 515 – DhirendraBisht May 26 '16 at 05:55
  • That's the result you're looking for, correct? Are you looking for the result in your first or second image in your post? – groovenectar May 26 '16 at 05:56
  • NO I want first sort the data by date and get the project id who have max date after that records below first row show all the date based on particular project id. like payment_date project_id 2016-07-25 517 2016-07-15 517 2016-07-08 517 .... 2016-07-21 514 2016-07-14 514 .. .. – DhirendraBisht May 26 '16 at 05:57
  • result i want like in the first image . – DhirendraBisht May 26 '16 at 05:58
  • Then just order by date only? In your result image, you have duplicates of both dates and project IDs... I think that your goal will need to be clearer for this question... – groovenectar May 26 '16 at 06:01
  • So in your result, you do not want duplicate dates. Only duplicate IDs – groovenectar May 26 '16 at 06:03
  • Duplicates records does not matters i want first latest project id and then get all the records of that project ids after that same for the other – DhirendraBisht May 26 '16 at 06:06
0

Firts order by should be payment_date (DES) and then projcet_id

SELECT payment_date, project_id FROM `payments` 
ORDER BY payment_date DESC, project_id DESC

Otherwise if you want firts order by project and the for the same project ny payment 

SELECT payment_date, project_id FROM `payments`
ORDER BY  project_id DESC,   payment_date DESC
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107
  • SELECT payment_date, project_id FROM `payments` ORDER BY payment_date DESC, project_id DESC query show results as in the second image but i want results as in the first image. – DhirendraBisht May 26 '16 at 06:00
  • @user4328056 .. I see only one image .. please update and forma properly your question .. – ScaisEdge May 26 '16 at 06:01
  • No .. i have checked .. the query i provided return the (second ) image result ......Which type is the column payment_date ? – ScaisEdge May 26 '16 at 06:04
  • payment_date column type is date – DhirendraBisht May 26 '16 at 06:10
  • Sorry but i think you did not understand i want results like in first image ..like I want first sort the data by date and get the project id who have max date after that records below first row show all the date based on particular project id. like payment_date project_id 2016-07-25 517 2016-07-15 517 2016-07-08 517 .... 2016-07-21 514 2016-07-14 514 .. .. – DhirendraBisht May 26 '16 at 06:17
  • wikisend.com/download/256870/payment.sql Here is my table data file – DhirendraBisht May 26 '16 at 06:58