MySQL sort 3 columns

Question

I am trying to sort MySQL result from qry_ledger_all_balance and it's working fine, but the problem is I can only sort by PaymentDate.

I want to sort in the order of:

PaymentDate value
Debit value
Credit value

I am unable to do that. I don't know what I am doing wrong. Here is my Table Structure Sample.

+-----------------+-------+--------+---------------------+----------------------------+
|   PaymentCode   | Debit | Credit |     PaymentDate     |         Particular         |
+-----------------+-------+--------+---------------------+----------------------------+
| CTM-41700000008 | 25000 |      0 | 2017-05-15 17:27:28 | Token Money From Customer1 |
| CTM-41700000007 | 12000 |      0 | 2017-05-15 17:26:26 | Token Money From Customer2 |
| CRV-11700000166 | 15000 |      0 | 2016-05-15 17:57:01 | Customer1 Receipt Vourcher |
| EPV-21700000012 |     0 |    150 | 2017-05-15 14:23:26 | Cash Outflow               |
| EPV-21700000004 |     0 |   1110 | 2017-05-15 14:06:48 | Cash Outflow               |
| EAS-41700000001 |     0 |  10000 | 2017-05-15 12:27:47 | Employee Advance Salary    |
+-----------------+-------+--------+---------------------+----------------------------+

Here is my query code in PHP

$branch_sql = "SELECT a.PaymentCode, a.Particular, a.Credit, a.Debit, 
Date(a.PaymentDate) AS PaymentDate, a.BranchID, a.CCode As RefPrint, 
a.RunningBalance, a.OpeningBalance, b.branchname FROM qry_ledger_all_balance AS a 
INNER JOIN tblbranches AS b 
ON a.BranchID = b.branchid 
WHERE DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') >= '$Start_Date' AND
DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') <= '$End_Date' 
ORDER BY a.PaymentDate ASC, a.Debit ASC, a.Credit ASC";

looks like you want to sort by date rather than datetime. try order by date(a.paymentdate), ... — Fabricator
– Fabricator, Commented Jul 11, 2017 at 21:59
So, you want to order by date, and then see all debits, then all credits? — Jacobm001
– Jacobm001, Commented Jul 11, 2017 at 21:59
@Jacobm001 Yes it's a cash ledger first order by date then all debits of that date then all credits of that date — Rtra
– Rtra, Commented Jul 11, 2017 at 22:01
The code you have shown us does what you describe as your objective, albeit very innefficiently. What you describe as your table structure is neither the structure of the tables you use in your query nor the output of the query. — symcbean
– symcbean, Commented Jul 11, 2017 at 22:01

Jacobm001 · Accepted Answer · 2017-07-11 22:02:25Z

1

Your order by clause needs to be slightly more clever. Putting a case statement in your order by will let you create a group like sorting element.

SELECT 
  a.PaymentCode, a.Particular, a.Credit, a.Debit, 
  Date(a.PaymentDate) AS PaymentDate, a.BranchID, 
  a.CCode As RefPrint, a.RunningBalance, 
  a.OpeningBalance, b.branchname 
 FROM qry_ledger_all_balance AS a 
 INNER JOIN tblbranches AS b 
   ON a.BranchID = b.branchid 
 WHERE DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') 
   >= '$Start_Date' 
 AND DATE_FORMAT(a.PaymentDate, '%Y-%m-%d') 
   <= '$End_Date' 
 ORDER BY 
  date(a.PaymentDate) ASC, 
  case 
    when a.Debit > 0 
      then 0
    else 1
  end ASC, 
  case 
    when a.Debit > 0 
      then a.Debit
    else a.Credit
  end ASC

answered Jul 11, 2017 at 22:02

Jacobm001

4,5595 gold badges32 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2017-07-11 22:07:59Z

1

Edit

I suspect you want

 ORDER BY DATE(a.PaymentDate), 
          CASE WHEN a.Debit > 0 THEN 1     /* nonzero debit */
               WHEN a.Credit > 0 THEN 2    /* zero debit, nonzero credit */
               ELSE 3 END,                 /* both zero, both nonzero */
          a.Debit, a.Credit

because if you don't use the DATE() function, MySQL will order by date and time.

By the way, your date selection criteria aren't sargable. This can cause serious performance problems when your tables get big.

Try this instead.

WHERE a.PaymentDate  >= '$Start_Date'
  AND a.PaymentDate  < '$End_Date' + INTERVAL 1 DAY

(Notice the < and the extra day at the end of the range. That will include all records with DATETIME values anytime on the $End_Date. )

If you have an index on a.PaymentDate this will allow that index to be range-scanned.

edited Jul 11, 2017 at 22:07

answered Jul 11, 2017 at 22:02

O. Jones

110k17 gold badges135 silver badges188 bronze badges

2 Comments

O. Jones Over a year ago

Somebody will hassle you about SQL injection vulnerabilities. That's great, it means I don't have to.

Rtra Over a year ago

Date sort works fine but the debit and credits won't sort properly

Collectives™ on Stack Overflow

MySQL sort 3 columns

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related