none
how to get a top cloumn value from the group of different records RRS feed

  • Question

  • orginal data:

    id   ro_no   cps    cpcom

    1      75    120    AAS
    2      80    100    AAS
    3      40    90      AAS
    4      56    140    AAS
    5      85    100    AAS
    6      78    104    AAS
    7      25    70      AA
    8      46    160    AA
    9      70    140    SA
    10    56    120    SA

     

    hi i want to get the top value of ro_no from the above specified cpcom data.the final output will be

    showed as

     

    o/p

    id   ro_no  cps    cpcom

    5      85    100    AAS

    8      46    160    AA

    9      70    140    SA

     

    please tel me how to write the query for this


    Sudhesh. G
    http://gurucoders.blogspot.com
    Tuesday, February 15, 2011 12:25 PM

Answers

  • hi Sudhesh,

    Take a look at this:

    DECLARE @Sample TABLE
        (
          id INT ,
          ro_no INT ,
          cps INT ,
          cpcom NVARCHAR(255)
        ) ;
    
    INSERT  INTO @Sample
    VALUES  ( 1, 75, 120, 'AAS' ),
            ( 2, 80, 100, 'AAS' ),
            ( 3, 40, 90, 'AAS' ),
            ( 4, 56, 140, 'AAS' ),
            ( 5, 85, 100, 'AAS' ),
            ( 6, 78, 104, 'AAS' ),
            ( 7, 25, 70, 'AA' ),
            ( 8, 46, 160, 'AA' ),
            ( 9, 70, 140, 'SA' ),
            ( 10, 56, 120, 'SA' ) ;
    
    SELECT  *
    FROM    @Sample O
    WHERE   ro_no = ( SELECT    MAX(I.ro_no)
                      FROM      @Sample I
                      WHERE     I.cpcom = O.cpcom
                    )
    ORDER BY id ;
    
    WITH    CTE
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER
                            ( PARTITION BY cpcom
                              ORDER BY ro_no DESC ) AS rownumber
                   FROM     @Sample
                 )
        SELECT  id ,
                ro_no ,
                cps ,
                cpcom
        FROM    CTE
        WHERE   rownumber = 1
        ORDER BY id ;

    Without indices the correlated sub-query example (the first one) has the better execution plan.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Sudhesh.G Thursday, February 17, 2011 2:29 PM
    Tuesday, February 15, 2011 12:38 PM

All replies

  • select 
      tab1.*
    from
    (
        Select 
          b.ID, b.ro_no, cps,cpcom,
          rank() over (partition by b.ro_no order by ro_no desc) as tRank
        from 
          tab b
        group by 
          b.ID, b.ro_no, b.cps,b.cpcom

      tab1
    where
      tab1.tRank <= 3

     

     

     

    Once you got your answer please mark it as answered so that others can use it..

    • Proposed as answer by Civic1986 Tuesday, February 15, 2011 12:28 PM
    Tuesday, February 15, 2011 12:28 PM
  • Try this:

    ;with cte as(select id, ro_no, cps, cpcom, row_number() over(partition by cpcom order by ro_no desc) as row)
    select id, ro_no, cps, cpcom
    from cte
    where row = 1
    


    ~Manu
    http://sqlwithmanoj.wordpress.com
    Tuesday, February 15, 2011 12:28 PM
  • As alternative (sorry cannot test it)

    SELECT * FROM tbl CROSS APPLY(SELECT TOP 1 FROM tbl t WHERE

      tbl.cpcom=t.cpcom ORDER BY ro_no   DESC) AS t


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, February 15, 2011 12:33 PM
    Answerer
  • hi Sudhesh,

    Take a look at this:

    DECLARE @Sample TABLE
        (
          id INT ,
          ro_no INT ,
          cps INT ,
          cpcom NVARCHAR(255)
        ) ;
    
    INSERT  INTO @Sample
    VALUES  ( 1, 75, 120, 'AAS' ),
            ( 2, 80, 100, 'AAS' ),
            ( 3, 40, 90, 'AAS' ),
            ( 4, 56, 140, 'AAS' ),
            ( 5, 85, 100, 'AAS' ),
            ( 6, 78, 104, 'AAS' ),
            ( 7, 25, 70, 'AA' ),
            ( 8, 46, 160, 'AA' ),
            ( 9, 70, 140, 'SA' ),
            ( 10, 56, 120, 'SA' ) ;
    
    SELECT  *
    FROM    @Sample O
    WHERE   ro_no = ( SELECT    MAX(I.ro_no)
                      FROM      @Sample I
                      WHERE     I.cpcom = O.cpcom
                    )
    ORDER BY id ;
    
    WITH    CTE
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER
                            ( PARTITION BY cpcom
                              ORDER BY ro_no DESC ) AS rownumber
                   FROM     @Sample
                 )
        SELECT  id ,
                ro_no ,
                cps ,
                cpcom
        FROM    CTE
        WHERE   rownumber = 1
        ORDER BY id ;

    Without indices the correlated sub-query example (the first one) has the better execution plan.


    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    • Marked as answer by Sudhesh.G Thursday, February 17, 2011 2:29 PM
    Tuesday, February 15, 2011 12:38 PM
  • btw, the ROW_NUMBER() approach has a general problem with non unique data, when no further candidate key exists. In such cases you cannot predict the returned values:

    DECLARE @Sample TABLE
        (
          id INT ,
          ro_no INT ,
          cps INT ,
          cpcom NVARCHAR(255)
        ) ;
    
    INSERT  INTO @Sample
    VALUES  ( 1, 75, 120, 'AAS' ),
            ( 2, 80, 100, 'AAS' ),
            ( 3, 40, 90, 'AAS' ),
            ( 4, 56, 140, 'AAS' ),
            ( 5, 85, 100, 'AAS' ),
            ( 6, 78, 104, 'AAS' ),
            ( 7, 25, 70, 'AA' ),
            ( 8, 46, 160, 'AA' ),
            ( 9, 70, 140, 'SA' ),
            ( 10, 56, 120, 'SA' ),
            ( 11, 70, 145, 'SA' ) ;
    
    SELECT  *
    FROM    @Sample O
    WHERE   ro_no = ( SELECT    MAX(I.ro_no)
                      FROM      @Sample I
                      WHERE     I.cpcom = O.cpcom
                    )
    ORDER BY id ;
    
    WITH    CTE
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER (
                              PARTITION BY cpcom
                              ORDER BY ro_no DESC ) AS rownumber
                   FROM     @Sample
                 )
        SELECT  id ,
                ro_no ,
                cps ,
                cpcom
        FROM    CTE
        WHERE   rownumber = 1
        ORDER BY id ;
    
    DELETE  FROM @Sample ;
    INSERT  INTO @Sample
    VALUES  ( 1, 75, 120, 'AAS' ),
            ( 11, 70, 145, 'SA' ),
            ( 2, 80, 100, 'AAS' ),
            ( 3, 40, 90, 'AAS' ),
            ( 4, 56, 140, 'AAS' ),
            ( 5, 85, 100, 'AAS' ),
            ( 6, 78, 104, 'AAS' ),
            ( 7, 25, 70, 'AA' ),
            ( 8, 46, 160, 'AA' ),
            ( 9, 70, 140, 'SA' ),
            ( 10, 56, 120, 'SA' ) ;
    
    SELECT  *
    FROM    @Sample O
    WHERE   ro_no = ( SELECT    MAX(I.ro_no)
                      FROM      @Sample I
                      WHERE     I.cpcom = O.cpcom
                    )
    ORDER BY id ;
    
    WITH    CTE
              AS ( SELECT   * ,
                            ROW_NUMBER() OVER (
                              PARTITION BY cpcom
                              ORDER BY ro_no DESC ) AS rownumber
                   FROM     @Sample
                 )
        SELECT  id ,
                ro_no ,
                cps ,
                cpcom
        FROM    CTE
        WHERE   rownumber = 1
        ORDER BY id ;

    Microsoft MVP Office Access
    https://mvp.support.microsoft.com/profile/Stefan.Hoffmann
    Tuesday, February 15, 2011 1:35 PM