SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL: Duplicates in Min Query

    I'm having a bit of bother with bit of Access SQL. This ought to be simple so I feel kinda dumb.

    The database includes a list of accommodation available for rent (properties table) and for each accommodation unit there is a price list (prices table)

    Prices
    priceid PK
    datefrom
    dateto
    price
    period (ie: per night, per week)
    currency (GBP/EUR)
    property_id FK

    I have a query to return the lowest price for an accommodation unit:

    SELECT Min(prices.price) AS minprice, prices.period AS minperiod, prices.propertyid, prices.currency AS mincurrency
    FROM prices
    GROUP BY prices.period, prices.propertyid, prices.currency;

    However in cases where there are prices shown per night and per week, the query returns double entries.

    minprice | minperiod | propertyid | mincurrency
    70 | per night | 40 | GBP
    250 | per week | 40 | GBP

    I'm guessing it would do the same if there were prices entered in EUR and GBP

    How can I amend my query just to return the lowest value?
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    SELECT Min(prices.price) AS minprice, prices.propertyid
    FROM prices
    GROUP BY prices.propertyid;

  3. #3
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah, but I need to return the period and currency fields in the output too.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by SortedSites View Post
    Yeah, but I need to return the period and currency fields in the output too.
    Thought so...

    Try this:
    Code:
    SELECT p1.price AS minprice, p1.period AS minperiod, p1.propertyid, p1.currency AS mincurrency
    FROM prices p1
    INNER JOIN
      (SELECT p2.propertyid, Min(p2.price) AS minprice
       FROM prices p2
       GROUP BY p2.priceid) as p3
    ON p1.propertyid = p3.propertyid
    AND p1.price = p3.minprice;

  5. #5
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Thought so...

    Try this:
    Code:
    SELECT p1.price AS minprice, p1.period AS minperiod, p1.propertyid, p1.currency AS mincurrency
    FROM prices p1
    INNER JOIN
      (SELECT p2.propertyid, Min(p2.price) AS minprice
       FROM prices p2
       GROUP BY p2.priceid) as p3
    ON p1.propertyid = p3.propertyid
    AND p1.price = p3.minprice;
    Error: "You tried to execute a query which does not include the field propertyid as part of an aggregate function."

    I altered the code so that p2.propertyid was added to the group by clause, it then runs but the execution returns only 3 results when there actually 33 different properties each with price schedules in the database.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT p1.price AS minprice
         , p1.period AS minperiod
         , p1.propertyid
         , p1.currency AS mincurrency
      FROM prices AS p1
    INNER 
      JOIN ( SELECT p2.propertyid
                  , MIN(p2.price) AS minprice
               FROM prices p2
             GROUP 
                 BY p2.propertyid ) AS p3
        ON p3.propertyid = p1.propertyid
       AND p3.minprice = p1.price
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT p1.price AS minprice
         , p1.period AS minperiod
         , p1.propertyid
         , p1.currency AS mincurrency
      FROM prices AS p1
    INNER 
      JOIN ( SELECT p2.propertyid
                  , MIN(p2.price) AS minprice
               FROM prices p2
             GROUP 
                 BY p2.propertyid ) AS p3
        ON p3.propertyid = p1.propertyid
       AND p3.minprice = p1.price
    99% there. One problem remains, if there is the same price (per night/week/currency), for two different date from/to periods then the query still returns a duplicate.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    well, they aren't complete duplicates, then, are they

    so, which of them do you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    well, they aren't complete duplicates, then, are they

    so, which of them do you want?
    No true, they're not, but when it comes to two which are the same price per night/week per currency - I only want to to select one of them, which one is immaterial. The first, the last, whichever makes for the easiest code.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    this is becoming more difficult to do without seeing any actual real data

    if there is another "duplicate" problem with multiple currencies, then just try to continue this pattern:
    Code:
    SELECT prices.propertyid
         , prices.price AS minprice
         , prices.period AS minperiod
         , prices.currency 
      FROM ( SELECT prices.propertyid
                  , prices.price AS minprice
                  , MIN(prices.period) AS minperiod
               FROM ( SELECT propertyid
                           , MIN(price) AS minprice
                        FROM prices 
                      GROUP 
                          BY propertyid ) AS p1
             INNER 
               JOIN prices 
                 ON prices.propertyid = p1.propertyid
                AND prices.price = p1.minprice
             GROUP
                 BY prices.propertyid
                  , prices.price ) AS p2
    INNER
      JOIN prices
        ON prices.propertyid = p2.propertyid
       AND prices.price = p2.minprice
       AND prices.period = p2.minperiod
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Hi - Sorry but that still doesn't eliminate the "duplicates" which appear because they are for different date periods.It still returns records 96 and 104 from the above display
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Bookmarks

Posting Permissions

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