[SOLVED] SQLite How to get row where value is related to max with group by

SQLite How to get row where value is related to max with group by

I am using SQLite3 in PHP 5.5.9
This is the query I am using

SELECT ForignKey_A, Location_ID, TimeStamp
FROM Events
GROUP BY ForignKey_A
ORDER BY TimeStamp

I want to get Location_ID on the rows where TimeStamp is at it's min and/or max value in each set of ForignKey_A (if i can do both at once that is great)

I thought I would get the first value in a group depending on how I sorted TimeStamp but it did not work, each group typically has 2 to 6 rows

I am not sure if this column will be useful for this, but
I do have column named type with boolean values of 1/0
type will = 0 on the row that MIN(TimeStamp) exist within each ForignKey_A group.
likewise type will = 1 on the row that MAX(TimeStamp) exist within each ForignKey_A group.

EDIT:
Sample Table:

╔═════════════╦═════════════╦════════════╦══════╗
║ ForignKey_A ║ Location_ID ║ TimeStamp  ║ Type ║
╠═════════════╬═════════════╬════════════╬══════╣
║           1 ║         762 ║ 1496062971 ║    0 ║
║           1 ║         427 ║ 1496063971 ║    1 ║
║           1 ║         417 ║ 1496065971 ║    0 ║
║           1 ║         123 ║ 1496072971 ║    1 ║
║           2 ║         594 ║ 1496062971 ║    0 ║
║           2 ║         427 ║ 1496072971 ║    1 ║
║           3 ║         217 ║ 1496082971 ║    0 ║
║           3 ║         356 ║ 1496092971 ║    1 ║
║           3 ║         985 ║ 1496099971 ║    0 ║
║           3 ║         789 ║ 1496162971 ║    1 ║
║           3 ║         456 ║ 1496262971 ║    0 ║
║           3 ║         123 ║ 1496362971 ║    1 ║
╚═════════════╩═════════════╩════════════╩══════╝

I did figure out if i do this i get the related Location_ID

SELECT MIN(TimeStamp), Location_ID
FROM TableA
GROUP BY ForignKey_A

I can get the data from the same row as MIN/MAX TimeStamp That gets me what i needed, I have to a couple sub select queries, but it works, but is there a way to do something like this and get the correct Location_ID?

SELECT MIN(TimeStamp) AS 'Begin', Location_ID AS 'Location_ID @ MIN TimeStamp',
    MAX(TimeStamp) AS 'End', Location_ID AS 'Location_ID @ MAX TimeStamp', 
    ForignKey_A
FROM TableA
GROUP BY ForignKey_A

That would make something like this

╔═════════════╦═══════╦════════════╦══════╦════════════╗
║ ForignKey_A ║ Start ║   Begin    ║ Stop ║    End     ║
╠═════════════╬═══════╬════════════╬══════╬════════════╣
║           1 ║   762 ║ 1496062971 ║  123 ║ 1496072971 ║
║           2 ║   594 ║ 1496062971 ║  427 ║ 1496072971 ║
║           3 ║   217 ║ 1496082971 ║  123 ║ 1496362971 ║
╚═════════════╩═══════╩════════════╩══════╩════════════╝

Solution

With a single query, there is no way with which MIN()/MAX() a column value is to be associated. You have to look up the minimums and maxmimums with subqueries, and then join these values together:

SELECT ForeignKey_A,
       b.TimeStamp,
       b.Location_ID,
       e.TimeStamp,
       e.Location_ID
FROM (SELECT ForeignKey_A,
             min(TimeStamp) AS TimeStamp,
             Location_ID
      FROM TableA
      GROUP BY ForeignKey_A
     ) AS b
JOIN (SELECT ForeignKey_A,
             max(TimeStamp) AS TimeStamp,
             Location_ID
      FROM TableA
      GROUP BY ForeignKey_A
     ) AS e
USING (ForeignKey_A);