0

I have a simple table similar to this. I need the number and text for the minimum action number.

The problem I have got that I did get the desired results in MySQL however my working environment is not able to do the same. It prints all unique text so for each number and minimum action i got all data. Anyway, I thought that sub query would work as I can cross link the same table to just get one line for the min(amount).

Firstly this is my desired outcome:

+-------------+--------+-------+
| min(action) | number | text1 |
+-------------+--------+-------+
|           1 |    108 | A1    |
+-------------+--------+-------+

This is my code for data:

CREATE TABLE t1
(

action int (10),
number int (10),
text1 text);


insert into T1 values 
( 1, 108,'A1'),
( 2, 108,'A2'),
( 3, 108,'A3'),
( 4, 108,'A4'),
( 5, 108,'A5');

This work in MySQL but not in my environment:

SELECT
min(action)
, number
, text1

FROM t1

GROUP by number;
, text1;

And this is something I need help with:

SELECT
t1.number,
t2.text1

FROM t1

LEFT JOIN
(
SELECT t2.action
    , t2.text1

FROM t1 AS t2 ) 

ON t1.action = t2.action

GROUP by t1.number;
Kalenji
  • 401
  • 2
  • 19
  • 42

1 Answers1

0

Use Sub Query Instead of using using join

First fetch the minimum number from the sub-query then on basis of it fetch the entire records

select * from t1 where action in (select min(action) from t1);

Rohit Kashyap
  • 21
  • 3
  • @ Rohit your code is not working. I fixed it but not sure why you put something in without checking. I still need join – Kalenji Feb 14 '19 at 13:56