mysql query the latest date
I am running this query
SELECT sh.*,
u.initials AS initals
FROM database1.开发者_如何学JAVAtable1 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.
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
精彩评论