Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    1

    Red face Unanswered: Need help with SQL Query...

    I have a table of following type data...
    Col1 Col2 Col3
    111 1 xxx
    111 2 yyy
    111 3 zzz
    222 1 abc
    222 2 def
    333 0 aaa
    333 1 bbb

    I need to write an query tp extract all data rows with the max(Col2) value for corresponsing Col1 e.g. query should retrieve the following rows in this case...

    111 3 zzz
    222 2 def
    333 1 bbb

    Can someone please assist with this??? I need this a bit quick, any assistance is appreciated. Thanks.

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    select * from table t1
    where
    t1.col2=(select max(col2) from table t2 where t1.col1=t2.col1)

    which will break if there are multiple maxes.

    select * from table
    inner join
    (select col1, max(col2) from table group by col1)
    x on (table.col1=x.col1)

    I think might also work. I'm struggling with this too for some reason although it's pretty straightforward!

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by LoztInSpace
    which will break if there are multiple maxes.
    no it won't

    he said "I need to write an query tp extract all data rows with the max(Col2) value for corresponsing Col1"

    note: "all rows with max"

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Question Is this what you want?

    SELECT intCol1, MAX(nvarText) AS Col1, MAX(intCol2) AS Col2
    FROM dbo.tblDbforum
    GROUP BY intCol1

    Returns
    111 3 zzz
    222 2 def
    333 1 bbb

    However, your post is a little ambiguous as it says 'extract all data rows'

    Table I created has 4 fields - Primary Key (Not used in this SQL), intCol1, intCol2 and nvarText
    Last edited by garethdart; 04-04-08 at 11:23.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

Posting Permissions

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