General discussion

Locked

sql query

By krcshanthi ·
what is the sql query to find the second maximum sal of emp table.sal is a numeric column of emp table.

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

sql query

by Glen_McLeod In reply to sql query

Try this (there are probably many others):

Select TOP 1 e1.sal
From emp e1
where e1.sal < (Select Max(e2.sal) From emp e2)
order by e1.sal DESC.

The sub-query will select the max first(1 value), then use it as the criteria for the main query.

This will work in both Access and SQL Server 6.5+, but not in Oracle. In Oracle there's a ROWID property (I think that's what it's called, it's been a while) that will let you limit the number of records returned by using it in the Where clause (ROWID = 1).

You also might try using Max(sal) in the main query instead of TOP 1, I haven't tried it, but it should work.

Glen

Collapse -

sql query

by Glen_McLeod In reply to sql query

Ok, just tried it and it should work anywhere:

Select Max(e1.sal)
From emp e1
where e1.sal < (Select Max(e2.sal) From emp e2)

Glen

Collapse -

sql query

by krcshanthi In reply to sql query

The question was auto-closed by TechRepublic

Collapse -

sql query

by simonchan In reply to sql query

SELECT MAX(sal) FROM emp
WHERE sal < (SELECT MAX(sal) FROM emp)

Collapse -

sql query

by krcshanthi In reply to sql query

The question was auto-closed by TechRepublic

Collapse -

sql query

by krcshanthi In reply to sql query

This question was auto closed due to inactivity

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Discussions

Related Forums