MySQL - sub query on the same table [duplicate]

24
February 12, 2019, at 09:30 AM

This question already has an answer here:

  • Get records with max value for each group of grouped SQL results 17 answers

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;
Answer 1

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);

READ ALSO
How to update image in mysql database using angularjs [on hold]

How to update image in mysql database using angularjs [on hold]

I am learning angularjsBut I am fetching some problem in image updating

44
mysqli_error() expects parameter 1 to be mysqli, null given ERROR

mysqli_error() expects parameter 1 to be mysqli, null given ERROR

I'm setting up a new server and having some issues with outdated coding

50
mysql composite primary key with dates [on hold]

mysql composite primary key with dates [on hold]

I have a table with 3 columns

11
Sql Query to present NULL rows

Sql Query to present NULL rows

I have a simples table:

38