Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Red face Unanswered: Max Value in a Select Statement

    I think it would be max to complete the following:

    The records in one table.

    Term Test
    1074 1-1
    1068 1-1
    1056 1-1

    1074 2-1
    1056 2-1

    1074 3-1

    Have 2 term fields retrieved. 1074 for 1 term field and just the prior term that the test was offered for the 2nd term field so it would look like:

    Term Test Prior Term

    1074 1-1 1068

    1074 2-1 1056

    1074 3-1 blank since this is the first term that the test was offered.

    Any help would be appreciated.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Assuming the clause 'order by test, term desc' defines your order.
    Code:
    SQL> select term, test, ld "PRIOR TERM"
      2    from (
      3  select t.*,
      4         dense_rank( ) over( partition by test order by term desc ) rnk,
      5         lead( term ) over( partition by test order by term desc ) ld
      6    from t
      7         )
      8   where rnk = 1
      9   order by test, term desc
     10  /
    
          TERM TES PRIOR TERM
    ---------- --- ----------
          1074 1-1       1068
          1074 2-1       1056
          1074 3-1
    
    SQL>

  3. #3
    Join Date
    Dec 2003
    Posts
    6
    Thanks JMartinez. I'm using a Query Manager web interface (Peoplesoft) which doesn't allow me to just type the select statement so I'm hoping to be able to figure out exactly how to add the dense_rank... part of your statement to my query (hopefully, it is possible). I appreciate the time you took to write up the select statement.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Can you write a VIEW and just call that from PeopleSoft?
    -cf

  5. #5
    Join Date
    Dec 2003
    Posts
    6
    Nope, I don't have the rights to create a view.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •