Tuesday, October 22, 2013

Mysql : Finding nth Max in table

CREATE TABLE `nmax` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nval` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;

select * from nmax

insert into nmax(nval)value(10);
insert into nmax(nval)value(-20);
insert into nmax(nval)value(30);
insert into nmax(nval)value(6);
insert into nmax(nval)value(8);
insert into nmax(nval)value(9);
insert into nmax(nval)value(4);
insert into nmax(nval)value(34);
insert into nmax(nval)value(2);
insert into nmax(nval)value(6);

select * from nmax

-- distinct values
select distinct nval from nmax order by nval desc

a) Finding Max Value for particular column
select max(nval) from nmax

b) Finding 3rd max value for particular column:

SELECT nval
FROM nmax t1
WHERE EXISTS( SELECT COUNT(*)
FROM nmax t2
WHERE t1.id <> t2.id
AND t1.nval < t2.nval HAVING COUNT(*) = 2 ) c) In general Finding nth max value for particular column: SELECT nval FROM nmax t1 WHERE EXISTS( SELECT COUNT(*) FROM nmax t2 WHERE t1.id <> t2.id
AND t1.nval < t2.nval HAVING COUNT(*) = n-1 ) d) Finding 3rd max value for particular column: select * from nmax where nval = (select distinct nval from nmax order by nval desc limit 3,1) e) In general Finding nth max value for particular column: select * from nmax where nval = (select distinct nval from nmax order by nval desc limit n,1) f) Finding 3rd max value for particular column: select id,nval from nmax E1 where 3 = (select count(distinct(nval)) from nmax E2 where E2.nval > E1.nval )

Explination:
Here inner query finds number of distinct entries greater than ‘E1.nval’ for each
‘nval’ in table ‘E1’ that will match 3 when E1.nval becomes 3rd max

g) In general Finding nth max value for particular column:
select id,nval from nmax E1 where
n = (select count(distinct(nval)) from nmax E2 where E2.nval > E1.nval ) 

No comments:

Post a Comment