OK this is waaaaaay off topic... but I post it in Odds and ends .... and might not get the answer... after I get the answer... I'll move it there.
Now this is a famous interview question...
From a column in a table how to find the second highest value.
There is 2 ways atleast... one is the nested query... which I know... I am looking for the other way... because the third highest, fourth highest, fifth etc... need more and more nesting with the nested query method... the other method is better.
Cool.
Srinath.

in Sql Server:
SELECT top 2
FROM table
ORDER BY descending

You could prolly translate top 2  into other databse talk (maybe?)

Oooooh... OK that will give me the first 2..... K we are getting somewhere... if I wanted just the second highest... The Db doesn't matter cos I have worked in all 6 (oracle, Sql server, sybase, informix, db2, teradata,and heck soem my SQL as well) I just wanna be SQL ready... not a clue what the actual env is going to be... cos I dont have a definete position yet...  :x
Cool.
Srinath.

You're definitely off my topic. :guns:

:lol:  :lol:  :lol:  :lol:

Depending on the database type you could do the following:

SELECT * from table_name_here order by what_you_want_to_order_by limit 1, 1


The limit 1, 1 means you want to start on the first record and show the next  one.  It might be limit 2, 1 too.. can't remember.

This WON'T work in MS ACCESS or SQL SERVER, but most other SQL databases should.


- Phobos

I cannot test these but I would guess the following (I am bored):

generically this might work:

SELECT COLUMNNAME FROM TABLE t
WHERE N=(SELECT COUNT(*) FROM TABLE WHERE
t.COLUMNNAME < COLUMNNAME)

where N is 2 or whatever..  Mega inefficient though

In oracle you could use rownum:

SELECT DISTINCT COLUMNNAME FROM TABLE
WHERE ROWNUM = 2
ORDER BY COLUMNNAME DESC

I think in SQLServer you could do this though it might need a groupby clause rather than distinct:

SELECT MIN(COLUMNNAME)
FROM
(
SELECT DISTINCT TOP 2 COLUMNNAME
FROM TABLE
ORDER BY COLUMNNAME DESC
)

But I expect there are better ways...

god that brings back memories...

SQL can be fun when your writing small statements, a massive 100+ line statement.. now that's a Buddha Loves You!

SELECT peon
FROM your_group
QUERY 'second largest number in table <table_name>'

Bluel39... Hey my post brought out the geek in you... in 17 months you have 8 posts, and this is one... I feel proud...
Oracle ...funny you mention that... at an interview at oracle they asked me and I tossed the nested query at them, and the interviewer asked me if I know about rownum... I thought she was referring to row ID...cos the position was in the CRM dev group, and the whole underlying premise of CRM is a reliable way to use row ID to select...and dang that lead me down the wrong path... BTWI didn't get the job... and a while later I tired that row num query in Db2... and it failed...
Cool.
Srinath.

And your first query was the answer I was looking for... Thanks. keep the ideas comming ... I have seen that in an interview the first question you face... you pretty much knock it out of the park and the next questions promptly become non issues... they dont even ask it...
Cool.
Srinath.
PS: tommorow I'll drag this topic off to odds and ends... where it belongs but he I got bluel39 out from hiding...

Glad to be of help...