CODEWITHSUNDEEP
mysqlsql
Rat2good
Rat2good

Reputation: 119

Selecting data MYSQL

In my MySQL database there are 2 tables:

Table-1:
COMMODITY_COLUMN
CommodityType1
CommodityType2
..............
CommodityType20


Table-2
RECORD_ID    COMMODITY_COLUMN       VALUE   DATE
1             CommodityType6         156     15/08/96
2             CommodityType13        56      25/12/16   
3             CommodityType5         756     15/02/05
.             CommodityType4         584
.             CommodityType13        48
.             CommodityType2         78
.             CommodityType2         8
.             CommodityType6         55
.             CommodityType14        7
.             CommodityType4         88
.             ..........................
128           CommodityType5         756     19/04/72
129           CommodityType2         999     25/04/85

Table-1 column "COMMODITY_COLUMN" used as foreign key in the Table-2. What I am trying to do is to collect LAST values of each 20 CommodityTypes from the Table-2.

I have tryied different MYSQL expressions such as:

SELECT TABLE-2.VALUE FROM TABLE-2 WHERE COMMODITY_COLUMN IN (SELECT COMMODITY_COLUMN FROM TABLE-1) ORDER BY RECORD_ID DESC LIMIT 1

But I am not succeeded. I can create 20 diffetent expression for each 20 commodity codes but I do not think it is good way of programming.

Could anybody suggest please a solution?

Upvotes: 0

Views: 73

Answers (3)

Unix One
Unix One

Reputation: 1181

SELECT
    t2.RECORD_ID,
    t2.COMMODITY_COLUMN,
    t2.VALUE,
    t2.`DATE`
FROM
    `TABLE-2` t2
    JOIN (SELECT
            MAX(RECORD_ID) AS RECORD_ID
          FROM
            `TABLE-2`
          GROUP BY
            COMMODITY_COLUMN) AS tt2 ON t2.RECORD_ID = tt2.RECORD_ID

Given the query you tried, I assumed RECORD_ID is the auto-incremented primary key, and that by "last" you meant last row inserted for that commodity.

Just to explain what's going on: the SELECT statement inside the JOIN gets the last/max RECORD_ID for every COMMODITY_COLUMN. Then that "table" tt2 is joined with t2 on RECORD_ID to get only those rows from t2.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

One method is a correlated subquery:

SELECT t2.*
FROM TABLE2 t2
WHERE t2.record_id = (SELECT MAX(tt2.record_id)
                      FROM table2 tt2
                      WHERE tt2.COMMODITY_COLUMN = t2.COMMODITY_COLUMN
                     ) ;

I don't think table1 has anything to do with the query.

Upvotes: 2

sagivmal
sagivmal

Reputation: 86

How about this: 

SELECT COMMODITY_COLUMN, VALUE, MAX(DATE) 
  FROM TABLE-2
    GROUP BY COMMODITY_COLUMN;

Upvotes: 0

Related Questions