There are tables:

  • User Profile - cmd_site_userprofile_imeiid
  • devices - gwcsu
  • device history

You need to select all devices for the user and the latest historical record for the device. I select all devices for the user:

SELECT "gwcsu"."id", "gwcsu"."imei", "gwcsu"."description", "gwcsu"."lastContainerName" FROM "gwcsu" INNER JOIN "cmd_site_userprofile_imeiid" ON ("gwcsu"."id" = "cmd_site_userprofile_imeiid"."gwcsu_id") WHERE "cmd_site_userprofile_imeiid"."userprofile_id" = 19

Then for each device I select the most recent historical record satisfying the conditions, the link between the history and the device according to the "history".

 SELECT "history"."id", "history"."imeiId", "history"."dateTime", "history"."latitude", "history"."longitude", FROM "history" WHERE ("history"."imeiId" = 1 AND NOT ("history"."latitude" IS NULL) AND NOT ("history"."longitude" IS NULL) AND NOT ("history"."dateTime" IS NULL)) ORDER BY "history"."dateTime" DESC LIMIT 1

This option works, but for a long time, tell me whether it is possible to make these two requests one (fast)? I tried to do this:

 SELECT "gwcsu"."id", "gwcsu"."imei", "gwcsu"."description", "gwcsu"."lastContainerName", ( SELECT "history"."imeiId", "history"."latitude" FROM "history" WHERE ( "history"."imeiId" = "gwcsu"."id" AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1 ) FROM "gwcsu" INNER JOIN "cmd_site_userprofile_imeiid" ON ("gwcsu"."id" = "cmd_site_userprofile_imeiid"."gwcsu_id") WHERE "cmd_site_userprofile_imeiid"."userprofile_id" = 19

But I get the error "ERROR: subquery must return only one column"

UPD. Following the advice of @Denis, I did this:

 SELECT "gwcsu"."id", "gwcsu"."imei", "gwcsu"."description", "gwcsu"."lastContainerName", ( SELECT "history"."latitude" FROM "history" WHERE ( "history"."imeiId" = "gwcsu"."id" AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1 ), ( SELECT "history"."longitude" FROM "history" WHERE ( "history"."imeiId" = "gwcsu"."id" AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1 ), ( SELECT "history"."dateTime" FROM "history" WHERE ( "history"."imeiId" = "gwcsu"."id" AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1 ) FROM "gwcsu" INNER JOIN "cmd_site_userprofile_imeiid" ON ("gwcsu"."id" = "cmd_site_userprofile_imeiid"."gwcsu_id") WHERE "cmd_site_userprofile_imeiid"."userprofile_id" = 19

It looks cumbersome, of course, but it works.

  • Try the first query better, and add all these columns to it. Updated the answer. - Denis

1 answer 1

Of course there will be an error, because you want to write in 1 column both "history"."imeiId" , and "history"."latitude" .

Try this:

 SELECT "gwcsu"."id", "gwcsu"."imei", "gwcsu"."description", "gwcsu"."lastContainerName", "h"."imeiId", "h"."latitude", "h"."longitude", "h"."dateTime" FROM "gwcsu" JOIN "cmd_site_userprofile_imeiid" ON ("gwcsu"."id" = "cmd_site_userprofile_imeiid"."gwcsu_id") JOIN (SELECT "history"."imeiId", "history"."latitude", "history"."longitude", "history"."dateTime" FROM "history" WHERE ( AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1) "h" ON "h"."imeiId" = "gwcsu"."id" WHERE "cmd_site_userprofile_imeiid"."userprofile_id" = 19

Or correct your request:

 SELECT "gwcsu"."id", "gwcsu"."imei", "gwcsu"."description", "gwcsu"."lastContainerName", ( SELECT "history"."latitude" FROM "history" WHERE ( "history"."imeiId" = "gwcsu"."id" AND not("history"."latitude" IS NULL) AND not("history"."longitude" IS NULL) AND not("history"."dateTime" IS NULL) ) ORDER BY "history"."dateTime" DESC LIMIT 1 ) FROM "gwcsu" INNER JOIN "cmd_site_userprofile_imeiid" ON ("gwcsu"."id" = "cmd_site_userprofile_imeiid"."gwcsu_id") WHERE "cmd_site_userprofile_imeiid"."userprofile_id" = 19
  • thank you very much! - avig