Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2009
    Posts
    7

    Unanswered: select max dat problem again

    hello every one
    i have table like this

    PR_NO CHANG_DAT APPR_PER APPR_DAT
    6 01/07/2009 8 31/03/2009
    6 01/07/2008 8 31/03/2008
    7 01/07/2007 8 31/03/2007
    7 01/07/2006 10 31/03/2006
    7 01/07/2005 10 31/03/2005

    i want to get recoreds with max chang_dat for each pr_no like this
    6 01/07/2009 8 31/03/2009
    7 01/07/2007 8 31/03/2007



    the sp which i wrote is

    create proc [dbo].[salary_career_appraisal_last_tst]
    as
    SELECT SAL_H.PR_NO, Max(SAL_H.CHANG_DAT) AS MaxOfCHANG_DAT, SAL_H.APPR_PER, SAL_H.APPR_DAT
    FROM SAL_H
    group by pr_no , APPR_PER , APPR_DAT

    but it give me many recoreds
    thank you for your help

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Top chang_dat per pr_no
    Code:
    SELECT pr_no
         , Max(chang_dat) As max_chang_dat
    FROM   sal_h
    GROUP
        BY pr_no
    Now join back to sal_h table to get the remaining information
    Code:
    SELECT *
    FROM   sal_h As a
     INNER
      JOIN (
            SELECT pr_no
                 , Max(chang_dat) As max_chang_dat
            FROM   sal_h
            GROUP
                BY pr_no
           ) As b
        ON b.pr_no = a.pr_no
       AND b.max_chang_dat = a.chang_dat
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nicely explained, george

    joining to a derived table is my favourite method as well

    here are several more: ~jk groupwise max
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    George, Rudy,
    Why not use ROW_NUMBER()? In all circumstances I have used it so far, the execution was faster. I only use derived tables for SQL Server 2000.
    Code:
    WITH cte as 
    (
    SELECT 
    	SAL_H.PR_NO, SAL_H.CHANG_DAT, SAL_H.APPR_PER, SAL_H.APPR_DAT
    	ROW_NUMBER() OVER (PARTITION BY SAL_H.PR_NO, APPR_PER, APPR_DAT 
    			ORDER BY SAL_H.CHANG_DAT DESC) as RowNum
    FROM SAL_H
    )
    SELECT SAL_H.PR_NO, SAL_H.CHANG_DAT AS MaxOfCHANG_DAT, SAL_H.APPR_PER, SAL_H.APPR_DAT
    FROM cte
    WHERE RowNum = 1
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Windowed functions are great, however, as you've pointed out, they are not portable to most other RDBMS'.

    If you're lucky enough to work in a purely Microsoft SQL Server 2005+ environment, then by all means use that method
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by gvee
    Windowed functions are great, however, as you've pointed out, they are not portable to most other RDBMS'.
    It works in DB2. I Don't know about other DBMS's
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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