How to get minimum value of a particular record column in SQL?

Question

Sql/Mysql

select * from test;

fare_str = varchar(100) datatype.

+-----------------+-------------+-------------+
| fare_str        | operator_id | bus_type_id |
+-----------------+-------------+-------------+
| 550,600,555     |           7 |          62 |
| 650,660         |           2 |          77 |
| 449,449,449,449 |           4 |          77 |
| 333             |          21 |          75 |
| 650             |           7 |          50 |
| 500             |           2 |          97 |
+-----------------+-------------+-------------+

required output: Here how can i get min value of a particular raw and column fare_str by sql query;

+-----------------+-------------+-------------+
| fare_str        | operator_id | bus_type_id |
+-----------------+-------------+-------------+
| 550             |           7 |          62 |
| 650             |           2 |          77 |
| 449             |           4 |          77 |
| 333             |          21 |          75 |
| 650             |           7 |          50 |
| 500             |           2 |          97 |
+-----------------+-------------+-------------+

Is min values are at first as show in table? – R D Jun 25 '14 at 11:25 — R D, Jun 25 '14 at 11:25
No any where it can be.... – user3676578 Jun 25 '14 at 14:11 — user3676578, Jun 25 '14 at 14:11

score 0 · Answer 1 · answered Jun 25 '14 at 10:56

Try this

    SELECT MIN(SUBSTRING_INDEX(SUBSTRING_INDEX(t.fare_str, ',', n.n), ',', -1)) value,operator_id, bus_type_id  
    FROM Table1 t CROSS JOIN 
      (
       SELECT a.N + b.N * 10 + 1 n
       FROM 
          (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
         ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
       ORDER BY n 
       ) n
    WHERE n.n <= 1 + (LENGTH(t.fare_str) - LENGTH(REPLACE(t.fare_str, ',', '')))
    Group By operator_id, bus_type_id

score 0 · Answer 2 · edited May 23 '17 at 12:10

0

Use Substring_Index, if min value is at first. Query for the same

SELECT Substring_Index(fare_str,',' ,1), operator_id, bus_type_id  from test

If min values are in between, then refer this thread :

"Reverse GROUP_CONCAT" in MySQL?

edited May 23 '17 at 12:10

Community

1
1

answered Jun 25 '14 at 11:07

R D

1,330
13
30

From this Substring_Index(fare_str,',' ,1) always it is coming first sub string only but i want min value. – user3676578 Jun 25 '14 at 14:10

How to get minimum value of a particular record column in SQL?

2 Answers2