Finding the n.th higest value

sberg43 · #1 (**permalink**) 02-13-04, 11:17

Hi.

I have a table, "Message", that contains information about several messages. On field in the table, "no_views" shows how many times a message has been viewed.

I want to be able to show the top three messages with the most views. How can this be done in sql?

A query against a table like this
MessageID | No_Views
1 | 10
2 | 110
3 | 200
4 | 150
5 | 5

should produce this output:
3 | 200
4 | 150
2 | 110

(The order is not important).

Thanks for your help!

r937 · #2 (**permalink**) 02-13-04, 11:31

Code:

select MessageID
     , No_Views
  from Message M
 where 3 > (
   select count(*)
     from Message 
     where No_Views > M.No_Views )

sberg43 · #3 (**permalink**) 02-13-04, 11:57

Perfect!

Thanks for a very smart solution :-)

Sowmyam · #4 (**permalink**) 02-17-04, 04:29

Rudy,
ok this is for n maximum values, if i wnat to print only nth max, say if i want 1st, 2nd , 3rd,.. max value.

how to do it in sql.

r937 · #5 (**permalink**) 02-17-04, 08:00

if you want only the nth entry, use n-1 in the query

for example, to find the 13th highest entry,

Code:

select MessageID
     , No_Views
  from Message M
 where 12 = (
   select count(*)
     from Message 
     where No_Views > M.No_Views )

in english, the query finds the row where there are 12 rows with a higher value, so that must be the row with the 13th highest value

Sowmyam · #6 (**permalink**) 02-17-04, 08:12

Ok. Thank u. Actully i tried it out and got the solution.

Internet Services

Web Development

Digital Marketing

Consumer Tech