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

Question

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 ║
╚═════════════╩═══════╩════════════╩══════╩════════════╝

Please provide some sample data and your desired output. (See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) for how to add some.) — CL., May 29 '17 at 12:48
Fixed, I did figure out a working solution, but I have to do two queries on the same table and join them — GM-Script-Writer-62850, May 29 '17 at 13:43

score 2 · Accepted Answer · answered May 29 '17 at 13:51

2

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);

answered May 29 '17 at 13:51

CL.

173,858
17
217
259

Is there anything I can do to make this run faster, this takes about 1 second; this is with the database and scripts stored on a RAM disk being proceed by a I5-4690K. https://pastebin.com/UkWinxKX this table has 328512 rows. – GM-Script-Writer-62850 May 29 '17 at 16:41
I was able to get it about 33% faster by using `WHERE type = ` – GM-Script-Writer-62850 May 29 '17 at 16:50
A two-column index on `ForeignKey_A` and `TimeStamp` should help. – CL. May 29 '17 at 18:05
I'm not sure what a index is... Do you mean forcing `ForeignKey_A + TimeStamp` to be unique? technically that can have a redundancy but only when `Type = 0`, but i do know that `Location_ID` combined with `ForeignKey_A` will be unique – GM-Script-Writer-62850 May 29 '17 at 18:44
An index is what you create with [CREATE INDEX](http://www.sqlite.org/lang_createindex.html). – CL. May 29 '17 at 20:26
I did `CREATE INDEX indexTest ON TableA (ForeignKey_A, TimeStamp);` and it actually took longer; Does it matter that the 500k+ rows are essentially the same thing repeating over and over again for each `ForeignKey` cause i am using sample data I made with a loop? – GM-Script-Writer-62850 May 29 '17 at 23:12
Your test data should be as real as possible. Anyway, try adding the third column to the index. – CL. May 30 '17 at 06:36
Placing a index on `(forignkey, type, timestamp)` had the best effect; `(forignkey, type)` saved 15% by adding time it varies by +/- 2% usually saving 50ms to 100ms; the type value will eliminate 1/2 the table in a search; also `(forignkey, type, timestamp)` is better than `(forignkey, timestamp, type)`; also type as a index by itself is a VERY bad idea – GM-Script-Writer-62850 May 31 '17 at 00:35
I meant `(foreignkey, timestamp, locationid)`, for a [covering index](http://www.sqlite.org/queryplanner.html#covidx). – CL. May 31 '17 at 06:30
Still slower than `(forignkey, type, timestamp)` I will decide on a index (s) to use after i have some actual data – GM-Script-Writer-62850 Jun 01 '17 at 00:57

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

1 Answers1