MySQL - Getting a List of Users by who logged in last

I have a list of users.  I collect their activity. I want to get a list of users, but I want to order it by who logged in last.  Some users haven't logged in in months and don't appear in the login table.  There are two tables.  One is a users table and the other is a loginTable.  The login time is recorded by a timestamp.  Here is one way of getting the list and ordering by the last login.


SELECT users.id, MAX(loginTable.timeIn), loginTable.userId  FROM  loginTable
right join users on loginTable.userId = users.id
ORDER BY MAX(loginTable.timeIn) DESC ;

The right join insures that we get all of the users, even those who don't appear in the loginTable.  This is a slow query for a large user table, but since we are collecting metrics it doesn't matter.

Hopes this help someone.

A Couple of MySQL Tips - Join on Three Tables and Select Results into a File

These are a couple of things that I had to do with a mysql database lately and I thought that I would pass on these tips.

We were debugging a problem where certain data elements were dimensioned across three tables.  While debugging program logic, we wanted to see the data aggregated by the common column across all three tables.  Here is the basic sql statement to do that:

SELECT * (or any number of columns separated by commas) FROM table1 join table2 ON table1.primarykey = table2.foreignkey

join table3 ON table2.primarykey = table3.foreignkey;

OK, so once you do that, you want to put it into a separate file so that you can ftp it from the server to the dev platform to analyze the data.  Here is how you select results into a file:

SELECT * (or any number of columns separated by commas) FROM table1 INTO OUTFILE '/tmp/table1.txt';

Hope this helps someone.

Java MySQL SQL Tip Comparing Timestamp in Column to Current DateTime

There are many ways to skin a cat. I have a database table where I have to compare a column timestamp with the current date/time as well as adding hours and minutes. I realize that there is an SQL NOW function, but I also need the now time converted to calendar to be able to add and subtract calendar units, and I construct the SQL statement in Java.

Here is the syntax of the snippet in Java for comparison to the current datetime:

java.util.Date utilDate = new java.util.Date();

java.sql.Timestamp sqlDate = new java.sql.Timestamp(utilDate.getTime());

try {

stmt = conn.createStatement();

String myQueryString = "SELECT * FROM sale_table where saleStart < {ts '" + sqlDate.toString() + "' }";