CODEWITHSUNDEEP
mysqlsql
user007
user007

Reputation: 75

MYSQL: Select the Max Values in VARCHAR Field from two different tables

I am newbie to MYSQL.There are two tables namely table A and table B.Just need the Max values from two different tables.
Expected Output:

AG/2016-17/P/046



  table_A                                 
       In_No
     AG/2016-17/P/01
     AG/2016-17/P/029
     AG/2016-17/P/030

     table_B
      In_No
 AG/2016-17/P/01
 AG/2016-17/P/046
 AG/2016-17/P/015

SQL:

select MAX(bv) from(
SELECT MAX(CAST(SUBSTRING(In_No, 14, length(In_No)-3) bv AS UNSIGNED)) FROM table_A
union all
SELECT MAX(CAST(SUBSTRING(In_No, 14, length(In_No)-3) bv AS UNSIGNED)) FROM table_B) as a

Something is wrong.It is not showing the proper output.Please suggest me.Thanks in advance.

Upvotes: 1

Views: 42

Answers (2)

Charif DZ
Charif DZ

Reputation: 14721

try this 

select MAX(bv) 
from(
    SELECT MAX(CAST(SUBSTRING(In_No, 14, length(In_No)) as UNSIGNED INTEGER)) as bv FROM table_A
    union all
    SELECT MAX(CAST(SUBSTRING(In_No, 14, length(In_No)) as UNSIGNED INTEGER)) as bv FROM table_B
)  as a

Upvotes: 0

Blank
Blank

Reputation: 12378

Try this:

SELECT In_No, substring_index(In_No, '/', 2) AS In_No_sub
FROM (
    SELECT In_No FROM table_A
    UNION ALL
    SELECT In_No FROM table_B
) t
ORDER BY substring_index(In_No, '/', -1) + 0 DESC
LIMIT 1

SQLFiddle Demo

Upvotes: 2

Related Questions