mysql query the latest date

Question

I am running this query

SELECT sh.*,
       u.initials AS initals
  FROM database1.table1 AS sh
  JOIN database2.user AS u ON u.userID = sh.userid
 WHERE id = 123456
   AND dts = ( SELECT MAX(dts) from database1.table1 ) 
ORDER BY sort_by, category

In the table1 I have records like this

dts                           status                    category         sort_by
2010-04-29 12:20:27        Civil Engineers          Occupation 1 
2010-04-28 12:20:27        Civil Engineers          Occupation 1 
2010-04-28 12:20:54 Married             Marital Status 2      2010-04-28 12:21:15 Smoker             Tobbaco    3
2010-04-27 12:20:27        Civil Engineers          Occupation 1 
2010-04-27 12:20:54 Married             Marital Status 2      2010-04-27 12:21:15 Smoker             Tobbaco    3
2010-04-26 12:20:27        Civil Engineers          Occupation 1 
2010-04-26 12:20:54 Married             Marital Status 2      2010-04-26 12:21:15 Smoker             Tobbaco    3

If you look at my data, I am choosing the latest entry by category and sort_id. however in some case such as on 29th (2010-04-29 12:20:27) I have only one record. So in this case I want to show occupation for latest and then the rest of them (latest). But currently it displays only one row. Thanks

Answer 1

trying as best to guess at what you have, your query would have to be adjusted anyhow, something like

SELECT 
      sh.dts,
      cast( if( preAgg1.totalPerCatSort = 1, sh.status, ' ' ) as char(20))
          as SingleStatus,
      sh.category,
      sh.sort_by, 
      u.initials AS initals 
   FROM 
      database1.table1 AS sh,
      database2.user AS u,
      ( select 
              t1.category, 
              t1.sort_by, 
              max(dts) maxdts,
              count(*) totalPerCatSort
           from 
              database1 t1
           group by 
              t1.category, 
              t1.sort_by ) preAgg1
   WHERE
         sh.userid = u.userID 
     and sh.sort_by = preAgg1.sort_by
     and sh.category = preAgg1.category
     and sh.dts = preAgg1.maxdts
     and id = 123456  ?? Should this actually be the UserID column ??
  ORDER BY 
     sort_by, 
     category 

you may have to apply the "ID = 123456" into the "preAgg1" sub-select, but again, not sure which ID that column / table was applicable for, otherwise, you could have other date entries by other "users" and not the same for the one candidate person you are looking for... Your call.

Per your concern of getting ALL records, I would just remove your "and ID = 123456" qualifier and all should be good