CODEWITHSUNDEEP
mysqlsql
user3576366
user3576366

Reputation: 15

How to resolve issue in order by date

  SELECT COUNT(patient_id) AS idpateint,
    patient_id
  FROM patient
  WHERE STR_TO_DATE(date_enter,'%d/%m/%Y' )
      BETWEEN STR_TO_DATE( '$repeat','%d/%m/%Y' ) AND STR_TO_DATE('$to','%d/%m/%Y')
  AND patient_type='opd'
  AND patient_id  ='$idpatient1'
  ORDER BY STR_TO_DATE(date_enter,'%d/%m/%Y' )

Actually I saved date in dd/mm/yyyy format in db. Order by is not working. No SQL error but date is not coming in order.

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270703

Here is your query:

select count(patient_id) as numpatients, patient_id
from patient
where STR_TO_DATE(date_enter, '%d/%m/%Y' ) between STR_TO_DATE('$repeat', '%d/%m/%Y' ) and 
                                                  STR_TO_DATE('$to', '%d/%m/%Y') and
       patient_type = 'opd' and
       patient_id = '$idpatient1'
order by STR_TO_DATE(date_enter, '%d/%m/%Y' )

You have a count() in the select. This turns the query into an aggregation query, so it only returns one row. In addition, you are selectxing only a single patient id. I could imagine that you want the counts by date, because you are so focused on date.

The following will give counts by date, regardless of the patient:

select STR_TO_DATE(date_enter, '%d/%m/%Y' ), count(patient_id) as numpatients
from patient
where STR_TO_DATE(date_enter, '%d/%m/%Y' ) between STR_TO_DATE('$repeat', '%d/%m/%Y' ) and 
                                                   STR_TO_DATE('$to', '%d/%m/%Y') and
       patient_type = 'opd' and
group by STR_TO_DATE(date_enter, '%d/%m/%Y' )
order by STR_TO_DATE(date_enter, '%d/%m/%Y' );

By the way. Don't you think the query looks ugly with all those calls to STR_TO_DATE(). They are ugly in addition to making the query less efficient. Store dates in the database using the database data types. That is what they are there for.

Upvotes: 2

Related Questions