database

[Amit Saste]

pls tell me to find out second highest element from the database using sql.

[Christophe Verré]

select SECOND_HIGHEST from DATABASE.
Don't blame me, this is a Meaningless Drivel after all.

[Mani Ram]

1. Select the highest element.
2. Delete it.
3. Select the highest element.
4. Insert the record you deleted in the step #2.

The record selected in step #3 should give you the second highest element.

[Joe Ess]

Originally posted by Mani Ram:

The record selected in step #3 should give you the second highest element.

Unfortunately you've changed the result by measuring it. What was once the second highest element is now the highest!

[Mani Ram]

Originally posted by Joe Ess:


Unfortunately you've changed the result by measuring it. What was once the second highest element is now the highest!

But I have nullified the changes by inserting the record again in step #4.
At the moment when I picked up the record, it was the highest. But at the end of step #4, what I have in hand is the second highest!

[David O'Meara]

You either know where the highest value is, or how fast it is moving, but not both.

[Michael Matola]

Silliness like this may work.

select max( pen.yourField )
from yourTable pen
where pen.yourField != ( select max( ult.yourField )
from yourTable ult )

Use at your own risk.
[ September 07, 2007: Message edited by: Michael Matola ]

[siva kumar]

Hi...

I will tell you something that you might find useful, I have faced such questions in interviews

The best way to handle questions in SQL is what my teacher told me long back to think in terms of sets.

The answer to your question might be something like

select * from table as tb where 2=(select count(distinct col) from table where tb.col<=col)

Just reflect on this query and have fun ( If you cannot understand then google for it)

[Joe Ess]

Originally posted by Mani Ram:


But I have nullified the changes by inserting the record again in step #4.
At the moment when I picked up the record, it was the highest. But at the end of step #4, what I have in hand is the second highest!

*shakes fist* I should know better than to post before my morning coffee!

[Jeanne Boyarsky]

Originally posted by Amit Saste:
pls tell me to find out second highest element from the database using sql.

In the event this is a serious question, please use the JDBC forum up top.

And my joking answer: use SQL to return all the elements. Find highest. Find second highest.

[David O'Meara]

Originally posted by Jeanne Boyarsky:
And my joking answer: use SQL to return all the elements. Find highest. Find second highest.

If you read them all starting from the bottom, the second last one is the second one.

[marc weber]

if(forum.equals(Forums.MEANINGLESS_DRIVEL)) {
  return (new Random()).nextBlurb();
}

[Peter Rooke]

Hey with the SQL

select HEAVIEST_ELEMENT from DATABASE.

I got this result set back:

"The heaviest element known to science was recently identified by
physicists.

The element, tentatively named Administratium, has no protons or electrons
and thus has an atomic weight of 0. However, it does have one neutron, 125
assistant neutrons, 745 vice-neutrons and 111 assistant vice-neutrons, for
an atomic number of 312. The 312 particles are held together by a force
that involves the continuous exchange of meson-like particles called morons.

Since it has no electrons, Administratium is inert. However, it can be
detected chemically, as it impedes every action with which it comes in
contact. According to the discoverers, one reaction that normally requires
less than one second, was extended to four days by the presence of a minute
amount of Administratium.

Administratium has a half-life of approximately three years, at which time
it does not actually decay, but instead undergoes a reorganization in which
assistant neutrons, vice-neutrons, and assistant vice-neutrons exchange
places. Some studies suggest that its atomic mass actually increases in
each reorganization.

Research at other laboratories indicates that Administratium occurs
naturally in the atmosphere. It tends to concentrate at certain points,
such as government agencies, large corporations, universities and churches, and can usually be found in the newest, best-appointed and best-maintained
buildings.

Scientists point out that Administratium is known to be toxic at any level
of concentration and can easily destroy any productive reaction where it is
allowed to accumulate. Attempts are being made to determine how
Administratium can be controlled to prevent irreversible damage, but
results to date are not promising."

Clever these database things - arn't they...

[Ryan McGuire]

In MS SQL Server 2005:

SELECT * FROM Table WHERE sortField in 
  (SELECT TOP(1) sortField FROM Table WHERE sortField in 
    (SELECT TOP(2) sortField FROM Table ORDER BY sortField DESC)
   ORDER BY sortField )

This is pretty flexible as well. If you want the eighth, ninth and tenth highest records, change the TOP(1) to TOP(3) and the TOP(2) to TOP(10).

Granted, it's not efficient, but it works.

[Sue Meng]

Originally posted by Amit Saste:
pls tell me to find out second highest element from the database using sql.

Pls go and find out second highest element from the database using sql.

[Rahul Bhattacharjee]

Isn't there a aggreate function for doing the same.

[Abhinav Srivastava]

http://punitive-surgery.lcs.mit.edu/scicache/27/scimakelatex.79532.find+out+second+highest+element+from+the+database+using+sql.+.html

[Sasi Rohitasha]

Try this query, works in DB2

SELECT MAX(COL_NAME) FROM TABLE_NAME
WHERE COL_NAME < ( SELECT MAX(COL_NAME) FROM TABLE_NAME)

-Thanks