Hi all
having extreme problems with a query

the folowing query gives me the wrong date

SELECT p_timeline.oid as ptimoid, p_timeline.fk_oid as ptimfk_oid,
p_timeline.TXDATE, p_timeline.TXMOD, p_timeline.TXREAS,TXMOD.codetext as treatment,
TXSUPR.codetext as Place, p_timeline.TXSUPR,
p_timeline.TXSITE, site.codetext as Unit,
p_timeline.TXACC, p_timeline.TXANAT, p_timeline.TXCATH, p_timeline.TXPDFL, p_timeline.TXUNIT_NEW
FROM p_timeline
left join codes as site on site.proid = p_timeline.TXSITE
left join codes as TXSUPR on TXSUPR.proid = p_timeline.TXSUPR
left join codes as TXMOD on TXMOD.proid = p_timeline.TXMOD
where p_timeline.fk_oid = '10888'
group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

it gives this
27525, 10888, 2010-03-10 00:00:00, 8320, , Transplant; Cadaver donor, , , 8200, St James's, , , , ,


if I change the query to show all data against the patient

SELECT p_timeline.oid as ptimoid, p_timeline.fk_oid as ptimfk_oid,
p_timeline.TXDATE, p_timeline.TXMOD, p_timeline.TXREAS,TXMOD.codetext as treatment,
TXSUPR.codetext as Place, p_timeline.TXSUPR,
p_timeline.TXSITE, site.codetext as Unit,
p_timeline.TXACC, p_timeline.TXANAT, p_timeline.TXCATH, p_timeline.TXPDFL, p_timeline.TXUNIT_NEW
FROM p_timeline
left join codes as site on site.proid = p_timeline.TXSITE
left join codes as TXSUPR on TXSUPR.proid = p_timeline.TXSUPR
left join codes as TXMOD on TXMOD.proid = p_timeline.TXMOD
where p_timeline.fk_oid = '10888'
-- group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

I get four lines and the last date is 2011-01-06

'60692', '10888', '2011-01-06 03:00:00', '8432', NULL, 'York Low Clearance Patient', 'Hosp', '8091', '8217', 'Beeston', NULL, NULL, NULL, NULL, NULL

'27525', '10888', '2010-03-10 00:00:00', '8320', NULL, 'Transplant; Cadaver donor', NULL, NULL, '8200', 'St James''s', NULL, NULL, NULL, NULL, NULL

'27526', '10888', '2006-10-04 00:00:00', '8301',
NULL, 'Haemodialysis', 'Hosp', '8091', '8200', 'St James''s', NULL, NULL, NULL, NULL, NULL

'27527', '10888', '2006-01-28 00:00:00', '8301', NULL, 'Haemodialysis', 'Hosp', '8091', '8200', 'St James''s', NULL, NULL, NULL, NULL, NULL

Not sure why this is happening, cannot use p_timeline.oid as the data was inported incorrectly for the test db.

Your help would be most appreciated, if I change the following line
order by p_timeline.TXDATE desc limit 1

it works perfectly, but when I look at all the patient records, I only get the last record.

Thank you all for all your time
Ralph

different Patient

1.before adding a new record

last record working perfectly

SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
group by p_timeline.fk_oid
order by p_timeline.TXDATE desc


'40899', '28640', '2009-11-19 00:00:00', '8301', NULL, '8092', '8202', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL


before adding new record all records

SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
-- group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

'40899', '28640', '2009-11-19 00:00:00', '8301', NULL, '8092', '8202', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL
'40900', '28640', '2009-09-23 00:00:00', '8301', NULL, '8091', '8200', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL


Added a new record and then this starts to happen.
SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

'40899', '28640', '2009-11-19 00:00:00', '8301', NULL, '8092', '8202', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL


SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
-- group by p_timeline.fk_oid
order by p_timeline.TXDATE desc limit 1

'60693', '28640', '2012-06-08 04:00:00', '8310', NULL, '8090', NULL, NULL, NULL, NULL, NULL, NULL, '2012-06-08 16:08:33', NULL


SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
-- group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

'60693', '28640', '2012-06-08 04:00:00', '8310', NULL, '8090', NULL, NULL, NULL, NULL, NULL, NULL, '2012-06-08 16:08:33', NULL
'40899', '28640', '2009-11-19 00:00:00', '8301', NULL, '8092', '8202', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL
'40900', '28640', '2009-09-23 00:00:00', '8301', NULL, '8091', '8200', NULL, NULL, NULL, NULL, 'Leeds&Hull', '2012-03-24 09:22:40', NULL

Not sure what is happening have I got my syntax's right for seeing last record
i.e.
group by Patients id

Orderby date desc

Please your help will be most appreciated

ralph

Hi, check if you have data in all the colums you use for joins

Olav Alexander Mjelde

Quote:

last record working perfectly

SELECT * FROM renaltest.p_timeline
where p_timeline.fk_oid = '28640'
group by p_timeline.fk_oid
order by p_timeline.TXDATE desc

sorry, but if this is working correctly, it's only a coincidence


when you use the dreaded, evil "select star" along with GROUP BY, then all the columns other than the ones mentioned in the GROUP BY clause will have values that are indeterminate

the columns not mentioned in the GROUP BY clause are called "hidden"

see http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

notice the very first line --

Quote:

In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause.

mysql will go ahead and run your query, where it would fail in any other database systems

the manual also says --

Quote:

this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values the server chooses.

i hope this explains the results you are seeing

as for correcting it, you'll need a subquery which pulls that MAX date, and then join this back to your main table

see http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon