sql query

sql query to find the third maximum value of a column in a database

Page 1 of 1

3 Replies - 1283 Views - Last Post: 22 December 2008 - 04:30 PM Rate Topic: -----

#1 Mathar  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 22-December 08

sql query

Post icon  Posted 22 December 2008 - 12:26 AM

sql query to find the third maximum value of a column in a database
Is This A Good Question/Topic? 0
  • +

Replies To: sql query

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,127
  • Joined: 18-April 07

Re: sql query

Posted 22 December 2008 - 12:55 AM

One way you could do this is using a nested query...

select fieldhere from (select top 3 fieldhere from table order by fieldhere desc) as top3table order by fieldhere ascending



The top record will then be your third maximum. What essentially you are doing here is ordering your records in decending order, grabbing the top three records, then selecting those records from that inner table results and ordering them in ascending order, which means the top record is the third highest of the results.

If you are using mySQL you can use the limit clause instead...

select fieldhere from (select fieldhere from table order by id desc limit 3) as top3table order by id asc limit 1



That will give you the third maximum value as a single record.

Enjoy!

"At DIC we be third highest maximum finding code ninjas... we are always operating as maximum, not third maximum." :snap:
Was This Post Helpful? 0
  • +
  • -

#3 Hary  Icon User is offline

  • D.I.C Regular

Reputation: 44
  • View blog
  • Posts: 427
  • Joined: 23-September 08

Re: sql query

Posted 22 December 2008 - 02:41 PM

What about no subqueries:

SELECT * FROM table ORDER BY column DESC LIMIT 2, 1

Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5801
  • View blog
  • Posts: 12,640
  • Joined: 16-October 07

Re: sql query

Posted 22 December 2008 - 04:30 PM

I was going to say that sub queries are probably the only database provider agnostic way to do it, but "top" is also non standard.

This should work on anything that does standard SQL:
select max(fieldhere)
	from table
		where fieldhere < (
			select max(fieldhere)
				from table
				where fieldhere < (
					select max(fieldhere) from table
				)
			)



No, it ain't pretty. :P

This post has been edited by baavgai: 22 December 2008 - 04:31 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1