SQL Select row where column has certain length and max value

Question

Column_A	Column_B	Column_C
Value 1	111A001	some value 1
Value 1	111A002	some value 2
Value 2	112A001	some value 3
Value 2	112A002	some value 4
Value 2	112A003	some value 5
Value 2	112A004	some value 6
Value 3	115A001	some value 7
Value 3	115A002	some value 8
Value 3	115A003	some value 9
Value 4	117A	some value 10

Hallo everyone,

I have a question regarding SQL query.

From the table above, I want to get an output as below:

Column_A	Column_B	Column_C
Value 1	111A002	some value 2
Value 2	112A004	some value 6
Value 3	115A003	some value 9

Value 4 is not selected because in Column_B after 'A' there is nothing or it didn't have 3 digit. As you can see in the top table, value 1 has 2 value in column B, 111A001 and 111A002. I want to select 111A002. Value 2 has 4 value in column B, 112A001 - 112A004, I want to choose only 112A004. So the point is I want to select the highest value (in column_b) from each value in column_A.

I tried with max(column_B) but it only show Value 4, 117A, some value 10.

I apologize if the title is not correct.

Does this answer your question? [querying WHERE condition to character length?](https://stackoverflow.com/questions/6807231/querying-where-condition-to-character-length) — tgdavies, Jun 16 '21 at 06:31

score 0 · Answer 1 · answered Jun 16 '21 at 06:41

0

Maybe something like this is what you are looking for:

select 
  Column_A,
  max(Column_B) as Column_B,
  max(Column_C) as Column_C
from d
where length(substring(Column_B,locate('A', Column_B) + 1)) >= 3
group by Column_A

answered Jun 16 '21 at 06:41

James

2,954
2
12
25

Manu Manjunath · Answer 2 · 2021-06-16T07:16:11.493

select column_a,
max(column_b),
max(column_c) /* Depends if you want max of column_c or column c value corresponding to the max of column_b - Question wasn't very clear */
from table
where column_b not like '%A___' /* Because you said - "Value 4 is not selected because in Column_B after 'A' there is nothing or it didn't have 3 digit." */
group by column_a

You can use this if you know for sure, there will always be 3 digits after "A" or ___A% if its 3 digits before "A" (which also might be right, going by the data you've posted)

%A% might not work for you, since it'll also consider strings ending with "A"; whereas you need something after "A"

Even better would be to use a function like length(column_b) = 7 if you're sure that the string will have X characters. Not sure which database you're on. So the function might be different. Some DBs have CHAR_LENGTH() or LEN()

Edit: Just clarifying that ___ is 3 underscores. Wanted to clarify because it just looks like one big line

SQL Select row where column has certain length and max value

2 Answers2