[SOLVED] difficult select - help request

[RoyHB]

using MySQL 5.0.67-community and PHP 5.2.8

 

I have a MySQL table that has columns username, date,time and other columns that aren't relevant to the question.

username is a varchar that has multiple entries with the same name and multiple names (i.e. there may be 20 entries for 'joe', 12 for 'fred' and 1 for 'jack').  Date is a varchar in the format "20090513" (yyyymmdd) and time is a varchar in the format "173523" (hhmmss).

 

I need to run a select that will return a single row for each user in username that is the row with the users most recent date and time.

 

It's been about 15 years since I worked with SQL and my brain has evidently archived my former knowledge in a write-only directory.

 

thanks in advance if someone can save me 20 hours of experimenting.

[trq]

Firstly, you need to store dates and times in a format that can be used within queries. VARCHAR is for storing strings, not dates / times.

[RoyHB]

Fair enough.  I've changed the columns to date and time types.

 

Now what?

[kickstart]

Hi

 

Agree with the above about storing dates and times in a more appropriate column.

 

However if needs be (not tested):-

 

SELECT username, max(LastVisitTime)

FROM (SELECT username, STR_TO_DATE(CONCAT(`date`,`time`),'%Y%m%d%H%i%s') LastVisitTime FROM SomeMySqlTable)

GROUP BY username

 

All the best

 

Keith

[RoyHB]

Works great, thanks to all for help

[RoyHB]

Here's the SQL I wound up with...  (remember, I'm constructing this as a query from within PHP PySQL,  and I don't think that the STR_TO_DATE function is available but no matter, the time and date columns are now native MySQL TIME and DATE types).

 

SELECT username, MAX(dttime), date, time, latitude, longitude

FROM (select username, CONCAT('date','time') dttime, date, time, latitude, longitude FROM mydatabase.mytable) as xx

GROUP by username

 

The sql returns a data set with a single entry per username, but the entry returned is not the one with MAX(dttime).

i.e. it returns the wrong row.

 

I've also tried the simpler :

SELECT username, MAX(CONCAT('date','time')), date, time, latitude, longitude FROM rbdbicov_orbdata.orbdata GROUP by username

I'm looking for a single row per username that is the row containing the highest (latest) date and time value.

[RoyHB]

in the SELECT statements above, within the MAX(CONCAT( i had the column names surrounded by single quotes, they should be ` instead.  Changed it, still returns the wrong rows.

[kickstart]

Hi

 

STR_TO_DATE should work, but as its name suggests works to convert a string to a date, not date and time fields to a date / time.

 

However, when you say it doesn't bring back the latest row are you basing that on the date and time retrieved, or the latitude / longitude or the dttime field? You are doing a group by on the username field, but also trying to bring back other fields. The values of the group by field and the max should be fine, but the others are random.

 

Does the table have a unique key field?

 

Try something like this (not tested):-

 

SELECT Deriv1.username, Deriv1.dttime), Deriv2.PosDate, Deriv2.Postime, Deriv2.latitude, Deriv2.longitude 
FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM mydatabase.mytable GROUP BY username) AS Deriv1
LEFT OUTER JOIN  (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS PosDate, time AS PosTime, latitude, longitude ) AS Deriv2
ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime

 

Also, best to avoid having columns on your table called date or time. They are a pain to remember to surround with back ticks.

 

All the best

 

Keith

[RoyHB]

I tried the SQL you suggested.  I removed the right paren from after the first Deriv1.dttime as it was unmatched.

 

When I ran it I got the following error...

 

Error

SELECT Deriv1.username, Deriv1.dttime, Deriv2.PosDate, Deriv2.Postime, Deriv2.latitude, Deriv2.longitude

FROM (

SELECT username, MAX( CONCAT( `date` , `time` ) ) AS dttime

FROM zzzzzzzz_orbdata.orbdata

GROUP BY username  <---ERROR HERE

) AS Deriv1

LEFT OUTER JOIN (

 

SELECT username, CONCAT( `date` , `time` ) AS dttime, date AS PosDate, time AS PosTime, latitude, longitude

) AS Deriv2 ON Deriv1.username = Deriv2.username

AND Deriv1.dttime = Deriv2.dttime

 

MySQL said: Documentation

#1054 - Unknown column 'username' in 'field list'

 

Thanks for your help - we'll power through this somehow

[kickstart]

Hi

 

Ooops, as I said not tested and I missed a FROM clause .

 

SELECT Deriv1.username, Deriv1.dttime, Deriv2.PosDate, Deriv2.Postime, Deriv2.latitude, Deriv2.longitude 
FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM mydatabase.mytable GROUP BY username) AS Deriv1
LEFT OUTER JOIN  (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS PosDate, time AS PosTime, latitude, longitude FROM mydatabase.mytable ) AS Deriv2
ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime

 

All the best

 

Keith

[RoyHB]

Thanks Keith;

 

Tested, works as required, good solution.  You're a champ.

 

Roy

[RoyHB]

One other thing.  I need to add a WHERE clause to the (now working) select statement.  I've tried adding one in a few places but in each case I've either gotten gotten an SQL error message or else the WHERE seems to have no effect.

 

Most recently I tried:

 

$query = "SELECT Deriv1.username, Deriv1.dttime, Deriv2.posdate, Deriv2.postime, Deriv2.latitude, Deriv2.longitude, Deriv2.speed, Deriv2.heading

    FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM myDataBase.myTable GROUP BY username) AS Deriv1

    LEFT OUTER JOIN (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS posdate, time AS postime, latitude, longitude, speed, heading

    FROM myDataBase.myTable) WHERE username = $desired_name AS Deriv2

    ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime";

 

This yields an SQL syntax error

 

I've also tried:

$query = "SELECT Deriv1.username, Deriv1.dttime, Deriv2.posdate, Deriv2.postime, Deriv2.latitude, Deriv2.longitude, Deriv2.speed, Deriv2.heading

      FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM rbdbnet_orbdata.orbdata GROUP BY username) AS Deriv1

      LEFT OUTER JOIN (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS posdate, time AS postime, latitude, longitude, speed, heading

      FROM rbdbnet_orbdata.orbdata) AS Deriv2

      ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime AND Deriv1.username = $desired_name";

 

This processed but returned all user names, not just the one I wanted.

[kickstart]

Hi

 

Try either:-

 

$query = "SELECT Deriv1.username, Deriv1.dttime, Deriv2.posdate, Deriv2.postime, Deriv2.latitude, Deriv2.longitude, Deriv2.speed, Deriv2.heading
      FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM rbdbnet_orbdata.orbdata GROUP BY username) AS Deriv1
      LEFT OUTER JOIN (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS posdate, time AS postime, latitude, longitude, speed, heading 
      FROM rbdbnet_orbdata.orbdata) AS Deriv2
      ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime
      WHERE Deriv1.username = $desired_name";

 

or

 

$query = "SELECT Deriv1.username, Deriv1.dttime, Deriv2.posdate, Deriv2.postime, Deriv2.latitude, Deriv2.longitude, Deriv2.speed, Deriv2.heading
      FROM (SELECT username, MAX(CONCAT(`date`,`time`)) AS dttime FROM rbdbnet_orbdata.orbdata WHERE username = $desired_name GROUP BY username) AS Deriv1
      LEFT OUTER JOIN (SELECT username, CONCAT(`date`,`time`) AS dttime, date AS posdate, time AS postime, latitude, longitude, speed, heading 
      FROM rbdbnet_orbdata.orbdata WHERE username = $desired_name) AS Deriv2
      ON Deriv1.username = Deriv2.username AND Deriv1.dttime = Deriv2.dttime";

 

All the best

 

Keith