By Mim Awnaf


2018-06-13 14:36:16 8 Comments

how can i add an "order by created_on asc" in this request :

(select user.first_name as prenom, user.last_name as nom, fvll.created_on, fvll.bar_code, "R" from stk_fuel_voucher_line fvll,stk_fuel_voucher fv, adm_user user 
where YEAR(fvll.created_on)=? and MONTH(fvll.created_on) = ? and user.id=fv.id_user and fv.id=fvll.id_fuel_voucher and
fvll.bar_code not in 
(select fvl.bar_code
from stk_fuel_voucher_line fvl, stk_fuel_voucher_book fvb
where fvl.bar_code >= fvb.first_bar_code and fvl.bar_code <=fvb.last_bar_code
and YEAR(fvl.created_on)=? and MONTH(fvl.created_on) = ?))

UNION

(select user2.first_name as prenom, user2.last_name as nom, fvll2.created_on, fvll2.bar_code, "B"
from stk_fuel_voucher fv2, stk_fuel_voucher_book fvb2, stk_fuel_voucher_line fvll2, adm_user user2 
where fvll2.bar_code >= fvb2.first_bar_code and fvll2.bar_code <=fvb2.last_bar_code and user2.id=fv2.id_user and fv2.id=fvll2.id_fuel_voucher
and YEAR(fvll2.created_on)=? and MONTH(fvll2.created_on) = ?)

1 comments

@spencer7593 2018-06-13 16:09:35

It looks to me like the two queries are the same, with the only difference being detecting whether there's a matching bar_code, and returning 'B' or 'R' depending.

I'd avoid the redundant rigmarole of the UNION and just do one query, with a conditional test to determine whether a 'B' or 'R' is returned.

If the intent of the UNION operator (in place of the more usual UNION ALL) is to remove duplicates from each set, we can use a GROUP BY clause or DISTINCT keyword to achieve that. (In the original query, we are guaranteed that there won't be duplicates between the two sets, on set always as an 'R', the other set always has a 'B'.

I don't have an understanding of the specification for the query, but based on what I am able to discern from the existing query, I would tend to do something like this instead:

SELECT user.first_name    AS prenom
     , user.last_name     AS nom
     , fvll.created_on    AS created_on
     , fvll.bar_code
     , CASE WHEN ni.bar_code IS NULL THEN 'R' ELSE 'B' END AS r
  FROM stk_fuel_voucher_line fvll
  JOIN stk_fuel_voucher fv
    ON fv.id = fvll.id_fuel_voucher
  JOIN adm_user user
    ON user.id = fv.id_user
  LEFT
  JOIN ( SELECT fvl.bar_code
           FROM stk_fuel_voucher_line fvl
           JOIN stk_fuel_voucher_book fvb
             ON fvb.first_bar_code <= fvl.bar_code
            AND fvb.last_bar_code  >= fvl.bar_code
          WHERE YEAR(fvl.created_on)  = ?
            AND MONTH(fvl.created_on) = ?
          GROUP BY fvl.bar_code
       ) ni
    ON ni.bar_code = fvll.bar_code
  WHERE YEAR(fvll.created_on)  = ?
   AND MONTH(fvll.created_on) = ?

 GROUP
    BY user.first_name    AS prenom
     , user.last_name     AS nom
     , fvll.created_on
     , fvll.bar_code
     , CASE WHEN ni.bar_code IS NULL THEN 'R' ELSE 'B' END

 ORDER
    BY fv11.created_on

Again, if we aren't concerned with removing duplicates, then we could remove the GROUP BY clause.

For the date comparisons, I'd opt for comparing the raw dates, so the query could make effective use of an index range scan operation.

Rather than this:

   WHERE YEAR(fvll.created_on)  = ?
     AND MONTH(fvll.created_on) = ?

I would write something like

  WHERE fvll.created_on >=  month_begin_dt + INTERVAL 0 MONTH
    AND fvll.created_on >=  month_begin_dt + INTERVAL 1 MONTH

with month_begin_dt representing an expression that returns the first day of the month, however that needs to get passed in, if we need to construct a DATE from a year and a month, we could do that. The end goal would be to have equivalent to:

  WHERE fvll.created_on >=  '2018-05-01' + INTERVAL 0 MONTH
    AND fvll.created_on >=  '2018-05-01' + INTERVAL 1 MONTH

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Which MySQL data type to use for storing boolean values

16 Answered Questions

[SOLVED] How do I connect to a MySQL Database in Python?

  • 2008-12-16 21:49:09
  • Marc Lincoln
  • 961973 View
  • 955 Score
  • 16 Answer
  • Tags:   python mysql

9 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

27 Answered Questions

[SOLVED] How to output MySQL query results in CSV format?

36 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

19 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

33 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

14 Answered Questions

[SOLVED] How to get a list of MySQL user accounts

  • 2009-07-16 03:23:53
  • burntsugar
  • 1331876 View
  • 1204 Score
  • 14 Answer
  • Tags:   mysql mysql5

3 Answered Questions

[SOLVED] Select last N rows from MySQL

  • 2012-08-25 21:46:32
  • nanoba
  • 162106 View
  • 83 Score
  • 3 Answer
  • Tags:   mysql database

1 Answered Questions

[SOLVED] Get next and previous row based on SQL ordered by two text columns

  • 2015-02-02 19:17:41
  • Wesley
  • 67 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql sql

Sponsored Content