CODEWITHSUNDEEP
mysqlsql
Vasanth
Vasanth

Reputation: 365

Find max value from the table values

I need to write the query to find the max value from the table value.

Table structure as follows,

col  col2  col3
1    a1    20150102
2    c1    20150201
3    a1    20150301
4    c1    20150101

I want the result as follows

output: table1

c1 = 20150201
a1 = 20150301

my query:

Select * from table1 where col3 = (select max(col3) from table1);

Upvotes: 1

Views: 37

Answers (2)

mohan111
mohan111

Reputation: 8865

this way also we can do using Row_number 

DECLARE @Table1 TABLE 
    (col int, col2 varchar(2), col3 int)
;

INSERT INTO @Table1
    (col, col2, col3)
VALUES
    (1, 'a1', 20150102),
    (2, 'c1', 20150201),
    (3, 'a1', 20150301),
    (4, 'c1', 20150101)
;

select T.col2,T.col3 from @Table1 T 
INNER JOIN (select RN from (select (ROW_NUMBER()OVER(PARTITION BY col2 ORDER BY col3))RN from @Table1)T GROUP BY T.RN )TT ON T.col = TT.RN
ORDER BY T.col2 DESc

Upvotes: 0

Matt
Matt

Reputation: 15061

Use GROUP BY and the MAX function.

SELECT col2, MAX(col3)
FROM table1
GROUP BY col2

Output

col2    MAX(col3)
a1      20150301
c1      20150201

SQL Fiddle: http://sqlfiddle.com/#!9/73026/1/0

Upvotes: 2

Related Questions