Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Question Unanswered: Get min value from column

    Greetz to all, I have a question about getting the min value of record from some table when multiple records have same code, like example:

    ID CODE NAME PRICE
    1 1234 Hardware 200.00
    2 1234 Hardware 230.00
    3 5678 Software 400.00
    4 5678 Software 430.00


    The query that I am looking for will return minimum values of PRICE column for all records in table, example:

    ID CODE NAME PRICE
    1 1234 Hardware 200.00
    3 5678 Software 400.00

    I've know for MIN(PRICE) function,but that only returns a single row for minimum value of column price.Any guidance appreciated. Greetz 8)

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT code
         , name
         , MIN(price) AS lowest_price
      FROM daTable
    GROUP
        BY code
         , name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2012
    Posts
    84
    Few examples:

    #1 using group by
    Code:
    select * from tabb
    join (
    	select code, min( price ) as minprice
    	from tabb
    	group by code
    ) AS x
    on ( x.code, x.minprice ) = ( tabb.code, tabb. price );
    #2 using correlated subquery
    Code:
    select * from tabb
    where price = (
    	select min( price ) 
    	from tabb as x
    	where x.code = tabb.code
    )
    #3 Using analytic function
    Code:
    select id, code, name, price
    from 
    (
    	select tabb.*,
    		min( price ) over ( partition by code ) as minprice
    	from tabb
    ) as alias
    where price = minprice

  4. #4
    Join Date
    Apr 2012
    Posts
    2
    Thank you both, greetz.

Posting Permissions

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