#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    154
    Rep Power
    14

    Finding all rows with second max value


    Hi,

    I'm making use of the Derby DB, but looking for a non-DB specific solution to the following problem.

    I have the following table:
    Code:
    id   product_name        price  date_price_observed
    1    chocolate           3.00  17/08/13
    2    bread               1.50  17/08/13
    3    milk                0.60  17/08/13
    4    chocolate           3.00  19/08/13
    5    bread               1.50  19/08/13
    6    milk                0.60  19/08/13
    7    water               0.55  19/08/13
    8    chocolate           3.00  22/08/13
    9    bread               1.50  22/08/13
    10   milk                0.60  22/08/13
    11   water               0.55  22/08/13
    I would like to write an SQL query to return all rows whose date_price_observed is MAX -1. The query should return the following rows:

    Code:
    id   product_name        price  date_price_observed
    4    chocolate           3.00  19/08/13
    5    bread               1.50  19/08/13
    6    milk                0.60  19/08/13
    7    water               0.55  19/08/13
    Many Thanks in advance.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by markb_1984
    ...looking for a non-DB specific solution
    Code:
    SELECT id
         , product_name        
         , price  
         , date_price_observed
      FROM daTable AS t
     WHERE 1 =
           ( SELECT COUNT(*)
               FROM daTable
              WHERE product_name = t.product_name     
                AND date_price_observed < t.date_price_observed )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    154
    Rep Power
    14
    Hi r937,

    since I posted my request, I came up with a solution, which might not be the optimal, but seems to do the job:

    Code:
    select product_name, price, date_price_observed
    from products
    where date_price_observed = (select max(date_price_observed)
    			     from products
    			     where date_price_observed < 
    						(select max(date_price_observed)
    					         from products))
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by markb_1984
    since I posted my request, I came up with a solution, which might not be the optimal, but seems to do the job:
    whatever works, eh

    i'd be interested in learning how the two queries compare performance-wise
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo