MySQL-how to make query for selecting min date and second min date with three other fields [duplicate]

This question already has an answer here:

I have a table with the following fields

+-------+------------+--------+------+
| Icode | PurDate | Qty | Rate |
+-------+------------+--------+------+
| 1 | 2018-08-19 | 10.000 | 5.35 |
| 2 | 2018-08-19 | 10.000 | 4.00 |
| 3 | 2018-08-19 | 15.000 | 4.30 |
| 1 | 2018-12-15 | 15.000 | 5.00 |
| 2 | 2018-12-15 | 12.000 | 5.00 |
| 1 | 2018-12-14 | 5.000 | 8.00 |
| 3 | 2018-12-14 | 7.000 | 6.00 |
+-------+------------+--------+------+


I want to select data from above table like

Select Min(Purdate) AS Date1, Qty, Rate From TableName Where Icode='1'


Please Help me

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers
3

Use a sub-select to locate the minimum date, then join to the to get the row matching that date.

SELECT
a.`Purdate` AS a.`Date1`, a.`Qty`, a.`Rate`
FROM `TableName` a
JOIN (SELECT MIN(`PurDate`) as `minDate`
FROM `TableName`
WHERE `Icode` = '1') b
ON b.`minDate` = a.`PurDate`
WHERE a.`Icode` = '1'


Since you don't need grouping you can just order by the date column and get the first two rows.

SELECT PurDate, Qty, Rate FROM TableName WHERE Icode = '1' ORDER BY PurDate LIMIT 2


use corelated sub query and union

select A.* from
(
select * from tablename t1 #1st min date will return
where t1.purdate in
（select min（purdate） from
tablename t2 where t2.icode=t1.icode
）
union


select t1.* from tablename t1 inner join
(SELECT
Icode
, Purdate
FROM (
SELECT
@row_num :=IF(@prev_value=Icode,@row_num+1,1) AS rn
, mp.Icode
, mp.Purdate
, @prev_value := Icode
FROM tablename mp
CROSS JOIN (SELECT @row_num :=1, @prev_value :='') vars
ORDER BY
mp.Icode
, mp.Purdate DESC
) d
WHERE rn = 2
) t2
on t1.Icode=t2.Icode and t1.Purdate=t2.Purdate
) as A where A.Icode in (......)