Results 1 to 3 of 3

Thread: Highest Value

  1. #1
    Join Date
    Jul 2012
    Posts
    2

    Unanswered: Highest Value

    I am trying to retrieve all rows with the largest value in a particular column. The largest value could return many rows for a particular users. Here is what I have thus far.

    SELECT DISTINCT
    ID, NAME, FOP, ACCT, CTNUM, ENDDATE, DEBIT, CREDIT, TRANSACTION_DATE, EXPORTED, CALENDAR_YEAR, FISCAL_YEAR, PAYROLL_IDENTIFIER,
    PAYROLL_NUMBER, [EARN-SEQNO], EVENT_SEQUENCE_NUMBER
    FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, ACCT, PAYROLL_NUMBER,EVENT_SEQUENCE_NUMBER
    ORDER BY EVENT_SEQUENCE_NUMBER DESC) AS RN
    FROM PAYROLLYEAREND ) s

    WHERE RN = 1 AND ID = '16443' AND PAYROLL_NUMBER ='7'

    In the above example, the EVENT_SEQUENCE_NUMBER is populated with values from 0 to 12. Could vary per user and PAYROLL_NUMBER. The query above returns 48 rows. However, all I want are the rows where EVENT_SEQUENCE_NUMBER is equal to the highest, which is in the above example is 12. The result would be 29 rows. The where clause is not part of overall query. Just isolating on one ID.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT DISTINCT 
       ID, NAME, FOP
    ,  ACCT, CTNUM, ENDDATE
    ,  DEBIT, CREDIT, TRANSACTION_DATE
    ,  EXPORTED, CALENDAR_YEAR, FISCAL_YEAR
    ,  PAYROLL_IDENTIFIER, PAYROLL_NUMBER, [EARN-SEQNO]
    ,  EVENT_SEQUENCE_NUMBER
       FROM PAYROLLYEAREND AS s
       WHERE  ID = '16443' 
          AND PAYROLL_NUMBER = '7'
          AND EVENT_SEQUENCE_NUMBER = (SELECT Max(z1.EVENT_SEQUENCE_NUMBER)
             FROM PAYROLLYEAREND AS z1
             WHERE  z1.ID = s.ID
                AND z1.PAYROLL_NUMBER = s.PAYROLL_NUMBER)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jul 2012
    Posts
    2

    Highest Value

    Thanks Pat! It appears to be working based on a couple IDs. Still going through the verifying process just to make sure.

    But thanks again.

Posting Permissions

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