General discussion

  • Creator
    Topic
  • #2091364

    sql query

    Locked

    by krcshanthi ·

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

All Comments

  • Author
    Replies
    • #3880136

      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

      • #3880102

        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

      • #3857808

        sql query

        by krcshanthi ·

        In reply to sql query

        The question was auto-closed by TechRepublic

    • #3880101

      sql query

      by simonchan ·

      In reply to sql query

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

      • #3857809

        sql query

        by krcshanthi ·

        In reply to sql query

        The question was auto-closed by TechRepublic

    • #3857807

      sql query

      by krcshanthi ·

      In reply to sql query

      This question was auto closed due to inactivity

Viewing 2 reply threads