Select distinct from most recent record
(1) By anonymous on 2021-03-23 21:01:12
Hi Friends, This is the same anonymous poster from [earlier today][1]. Thanks for the help provided by Stephan and Ryan! So now I have a new exercise so I thought I should make a new post. I want to select the most recent distinct record based on col1. The data is sorted by col1 so if you notice, the values in col2 get larger as it descends. `distinct` only works on one column and I can't figure how to group by col1. `select col1, col2 from t group by col1;` This seems to only get one record and not the most recent (highest) record. What are my options? ``` col1,col2 AAA,58000 AAA,60000 AAA,62000 AAA,64000 AAA,66000 AAA,68000 AAA,70000 AAA,74000 AAA,76000 AAA,78000 BBB,72000 BBB,78000 BBB,86000 BBB,90000 BBB,98000 BBB,102000 BBB,110000 BBB,112000 BBB,114000 BBB,146000 BBB,150000 BBB,154000 CCC,44000 CCC,48000 CCC,34000 CCC,40000 CCC,42000 CCC,46000 CCC,130000 CCC,132000 CCC,50000 CCC,52000 CCC,54000 CCC,76000 CCC,78000 ``` [1]:forumpost/aae86025fa
(2) By Ryan Smith (cuz) on 2021-03-23 21:37:10 in reply to 1 [link]
I cannot figure out what you mean. I'm *thinking* that you mean you want the "most recent" (as in highest number value in col2) item for every distinct value in col1, right? If that is the case simply: ``` SELECT col, MAX(col2) FROM t GROUP BY Col1; ``` This will of course not always work if you are looking to select many columns from that row, and other caveats, but before I talk about any of that, let's first see if this is what was meant and needed. When next asking a question, what really helps to alleviate any ambiguity, is to show us the intended results after the query ran successfully according to you. ``` Example: For these inputs/rows in my db: col1,col2 AAA,58000 AAA,60000 AAA,62000 AAA,64000 AAA,66000 AAA,68000 AAA,70000 AAA,74000 AAA,76000 AAA,78000 BBB,72000 BBB,78000 BBB,86000 BBB,90000 BBB,98000 BBB,102000 BBB,110000 BBB,112000 BBB,114000 BBB,146000 BBB,150000 BBB,154000 I want the query to produce results like this: col1,col2 AAA,78000 BBB,154000 ``` or some such.
(3) By anonymous on 2021-03-23 22:30:40 in reply to 2 [link]
Hi Ryan, You're right, I was a little sloppy with my post. Thank you for help in educating me. > I'm thinking that you mean you want the "most recent" (as in highest number value in col2) item for every distinct value in col1, right? Yes! Exactly correct. Why doesn't this work? ``` sqlite> select col1, max(col2) from t group by col1; AAA|78000 BBB|98000 ``` Using my sample data, BBB had a max of 154000 but here, it says 98000 is the highest. Even asking for highest `max(col2)` ignores 154000. ``` sqlite> select max(col2) from t; 98000 ```
(4.2) By Keith Medcalf (kmedcalf) on 2021-03-23 23:46:30 edited from 4.1 in reply to 3 [link]
The most likely answer is that col2 contains strings, not numbers. And a string starting with a 9 is bigger (sorts after) than any string starting with 0, 1, 2, 3, 4, 5, 6, 7, or 8.
(5) By anonymous on 2021-03-23 23:55:57 in reply to 4.2 [link]
> The most likely answer is that col2 contains strings, not numbers. How do I check that? There are no single quotes or double quotes around any of the values.
(6) By Ryan Smith (cuz) on 2021-03-24 00:23:53 in reply to 5 [link]
Easiest is to do: ``` SELECT col1, col2, typeof(col2) FROM t; ``` Assuming that what Keith suggested IS the case, this query should fix you right up: ``` SELECT col1, MAX(CAST(col2 AS INT)) FROM t GROUP BY col1 ; ``` Or this using a CTE or sub-query to first get the real (fixed) table values then grouping it: ``` -- WITH CTE: WITH tReal(c1,c2) AS ( SELECT col1, CAST(col2 AS INT) FROM t ) SELECT c1, MAX(c2) AS Latest FROM tReal ; -- Sub-Query: SELECT S.c1, MAX(S.c2) AS Latest FROM ( SELECT col1 AS c1, CAST(col2 AS INTEGER) AS c2 FROM t ) AS S ; ``` Note that these may produce errors or weird results if the columns do contain actual alpha characters and not just valid integer numeric values. Note also that technically SQLite does not require aliasing the sub-query (the "AS S" bit), but many other SQL engines do, so I used that in the example. You could also permanently "fix" the table, if the above assumption is what is going on and the above queries work, then this should work: ``` UPDATE t SET col2 = CAST(col2 AS INT) WHERE 1; ``` Backup your data before doing any such thing.
(7) By ThanksRyan on 2021-03-24 05:19:25 in reply to 6 [link]
Hi Ryan, Thanks for all the help! The type was definitely text so Keith's assumption was right. Thank you for the fast and helpful replies!