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 and 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) = ?))


(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 and
and YEAR(fvll2.created_on)=? and MONTH(fvll2.created_on) = ?)


@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 = fvll.id_fuel_voucher
  JOIN adm_user user
    ON = fv.id_user
  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) = ?

    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

    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

15 Answered Questions

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

  • 2009-07-16 03:23:53
  • burntsugar
  • 1384536 View
  • 1268 Score
  • 15 Answer
  • Tags:   mysql mysql5

35 Answered Questions

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

23 Answered Questions

[SOLVED] Finding duplicate values in MySQL

  • 2009-03-27 04:22:12
  • Jon Tackabury
  • 535741 View
  • 671 Score
  • 23 Answer
  • Tags:   mysql

21 Answered Questions

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

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

40 Answered Questions

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

29 Answered Questions

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

  • 2008-12-10 15:59:51
  • MCS
  • 998639 View
  • 1003 Score
  • 29 Answer
  • Tags:   mysql csv quotes

22 Answered Questions


10 Answered Questions

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

11 Answered Questions

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

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