SQLite Forum

Select distinct from most recent record
Login

Select distinct from most recent record

(1) By anonymous on 2021-03-23 21:01:12 [link] [source]

Hi Friends,

This is the same anonymous poster from earlier today. 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

(2) By Ryan Smith (cuz) on 2021-03-23 21:37:10 in reply to 1 [link] [source]

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 [source]

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] [source]

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] [source]

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] [source]

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] [source]

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!