Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    1

    Unanswered: max elements from table

    Hi,
    my table has COL1, COl2 with values below:

    COL1 COL2
    joy 10
    joy 11
    nik 10
    nik 11
    nik 12
    ale 11
    ale 12

    How can I select rows with max value for COL2?
    that is I want

    COL1 COL2
    joy 11
    nik 12
    ale 12

    Thanks in advance.
    Sunday.

  2. #2
    Join Date
    Jun 2002
    Location
    Belgrade
    Posts
    10
    SAMPLE Script : run it against some test DB in Query Analyzer .
    --=======================================

    declare @tbl table (COL1 int ,COL2 varchar(30) )

    insert into @tbl (COL1,COL2) values (1,'JOY')
    insert into @tbl (COL1,COL2) values (2,'JOY')
    insert into @tbl (COL1,COL2) values (3,'JOY')

    insert into @tbl (COL1,COL2) values (1,'NIK')
    insert into @tbl (COL1,COL2) values (2,'NIK')
    insert into @tbl (COL1,COL2) values (3,'NIK')

    insert into @tbl (COL1,COL2) values (1,'ALE')
    insert into @tbl (COL1,COL2) values (2,'ALE')
    insert into @tbl (COL1,COL2) values (3,'ALE')
    insert into @tbl (COL1,COL2) values (4,'ALE')
    insert into @tbl (COL1,COL2) values (5,'ALE')

    insert into @tbl (COL1,COL2) values (2,'ROY')
    insert into @tbl (COL1,COL2) values (3,'ROY')

    insert into @tbl (COL1,COL2) values (1,'STU')
    insert into @tbl (COL1,COL2) values (2,'STU')
    insert into @tbl (COL1,COL2) values (3,'STU')
    insert into @tbl (COL1,COL2) values (4,'STU')
    insert into @tbl (COL1,COL2) values (5,'STU')


    -- pure GROUP BY Clause
    SELECT Max(COL1) AS 'COL1',COL2 FROM @tbl GROUP BY COL2

    -- GROUP By Clause with additional ORDER BY
    SELECT Max(COL1) AS 'COL1',COL2 FROM @tbl GROUP BY COL2 ORDER BY COL1 DESC

    -- REFER to Books On Line for more information on the TOPIC
    --=============================================

    regards.
    srdjan

  3. #3
    Join Date
    May 2002
    Location
    Montréal, Canada
    Posts
    28
    Hi,
    What do you think about that one:

    I named my table 'tb', put alias 'a' and 'b' on it:

    _____________
    select a.* from tb a where col2 = (select MAX(col2) from tb b where b.col1 = a.col1)
    _____________
    Data Climber

Posting Permissions

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