CODEWITHSUNDEEP
mysql
Nalini Kishore
Nalini Kishore

Reputation: 23

How to get a maximum value of two columns from a single table?

Assume as I have an table named as Item, and Here I am going to give the sample table values and my expected out put

Item
------
Sl.No   Itemname    Rate    Date

1        A      200             15/01/2013
2        B      500             24/05/2013
3        C      600             12/06/2013
4        A      400             17/07/2013
5        A      550             18/08/2013
6        B      150             19/09/2013
7        C      140             20/10/2013


Expected Output:

Sl.No   Itemname    Rate    Date

1         A         550        18/08/2013
2         B         150        19/09/2013
3         C         140        20/10/2013

What is query can be written to get this output??

I have just tried out this query,

select * from postagitem group by categoryname where date = 
  (SELECT MAX(date) FROM postagitem);

But its not working, its really appreciable if anyone help me friends,

Thanks in Advance!

Thanks to all for your valuable response friends,

but still am not getting my correct output friends,

Actually i want to display the all the values with the condition of maximum date. For your clearance let me give my original table and values

Table Name: Price

City Gold_Rate Silver_Rate Date

Mumbai 3000 60 13-07-2014 Delhi 4000 50 14-04-2014 Bangalore 1400 40 16-06-2014 Mumbai 1500 58 18-09-2014 Mumbai 2500 54 19-08-2014 Delhi 1800 60 01-10-2014 Bangalore 1700 44 02-11-2014

Now the expected output will be

City Gold_Rate Silver_Rate Date

Mumbai 1500 58 18-09-2014 --> (the record of the maximum/recent date) Delhi 1800 60 01-10-2014 --> (the record of the maximum/recent date) Bangalore 1400 40 16-06-2014 --> (the record of the maximum/recent date)

How to write the query friends,

Thanks in Advance

Upvotes: 2

Views: 214

Answers (4)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28413

May be something like this

SELECT T1.M_SINO AS SL_NO,T.ITEMNAME,T.RATE,T.DATE 
FROM Test T INNER JOIN
     (SELECT Itemname,Max(date)MyDat ,Min(SL_NO) As M_SINO 
      FROM Test 
      GROUP BY Itemname) T1 ON
      T.Date = T1.MyDat AND
      T.Itemname = T1.Itemname

FIDDLE DEMO

Output:

Sl.No   Itemname    Rate       Date

1         A         550        18/08/2013
2         B         150        19/09/2013
3         C         140        20/10/2013

Upvotes: 0

ngrashia
ngrashia

Reputation: 9904

This query does the trick that you want:

SELECT B.MINSLNO, B.ITEMNAME, A.RATE, B.MAXDATE FROM
MY_TABLE A,
(
   SELECT MIN(SLNO) MINSLNO, ITEMNAME, MAX(DATEE) MAXDATE FROM MY_TABLE
   GROUP BY ITEMNAME
) B
WHERE A.DATEE = B.MAXDATE

OUTPUT:  (Gives the itemnames, min slno of the item, max date of the item, rate as on maxdate for the item)

 Minslno    |itemname| rate  | maxdate
    1       |A       |550    |  18-AUG-2013
    2       |B       |  150  |  19-SEP-2013
    3       |C       |140    |  20-OCT-2013

JS Fiddle Here

Upvotes: 0

Uriil
Uriil

Reputation: 12628

Probably this query will give you expected result:

SELECT * FROM `table` t1
WHERE (SELECT count(*) FROM `table` t2
       WHERE t1.Itemname = t2.Itemname AND
             t1.Date < t2.date) = 0

Same query, but this one might be simpler to understand:

SELECT *, (SELECT count(*) FROM `table` t2 WHERE t1.Itemname = t2.Itemname AND t1.Date < t2.date) AS `LaterRecords`
FROM `table` t1
HAVING `LaterRecords` = 0

Upvotes: 0

sumit
sumit

Reputation: 15464

IF BY MAX DATE

SELECT * FROM test t 
where date=(select max(date) from test t1 where t.itemname=t1.itemname)

Upvotes: 1

Related Questions