SQL Server Performance

min / max

Discussion in 'SQL Server 2005 General Developer Questions' started by arkiboys, Dec 5, 2007.

  1. arkiboys Member

    Hi,
    There is a table which holds prices. The data type is decimal(12, 4)
    There are many prices. The sample prices in this field is something like:
    100.1875
    100.1870
    98.390
    98.395
    99.000
    100.1880
    ...
    ...
    In a select query, I would like to treat numbers close to each other the same.
    For example:
    In the above sample prices i.e. 100.1875, 100.1880, 100.1870
    I would like to show only ONE of them i.e min value is 100.1870
    For the other prices;
    98.390
    98.395
    I would like the select query to show one of them (It is not important which one. i.e min value is 98.390
  2. Adriaan New Member

    The proper solution probably involves a function like ROUND() - look it up in Books Online. CAST() might also work, but you'll have to compare the results.
    In either case, add the DISTINCT keyword to remove the "duplicates".
  3. ranjitjain New Member

    Hi,
    At a first glance, it seems If you want to display min price then you can use min function by grouping all other columns and it will return the result withour any issues.
    But I still think it couldn't be that simple and you are looking for something else, post some sample data with desired result and the columns which will be involved in grouping.
  4. Adriaan New Member

    Looks like he wants to drop the fourth decimal - so use ROUND() or CAST() - and return unique values - so add DISTINCT.
  5. arkiboys Member

    Sample:
    let's say the prices are:
    114.18700000
    114.37000000
    114.18700000
    114.18800000
    114.18750000
    114.18700000

    Then;
    I woould like to see the following figures (Notice, 114.37000000 is missing)
    114.18700000
    114.18700000
    114.18800000
    114.18750000
    114.18700000

    so we are taking 114.18750000 as the starting point and then go half either way. Hope you know what I mean
  6. Adriaan New Member

    Your requirement seems to change with each posting - make up your mind what exactly you're looking for.
    Looks like you want to compare consecutive rows, where you take the value from the first row and start looking for the next row that has a value that is within a range of +/- .0001. You're not showing the rows inbetween.
    And presumably, if there is no match for the first row, then you want to start with the second row, etc. etc.
    Perhaps this gets you underway:
    SELECT t1.price FROM table t1
    WHERE EXISTS
    (SELECT t2.* FROM table t2
    WHERE ABS(t2.price - t1.price) <= .0001 AND t2.key <> t.1key)
  7. FrankKalis Moderator

    [quote user="arkiboys"]
    Sample:
    let's say the prices are:
    114.18700000
    114.37000000
    114.18700000
    114.18800000
    114.18750000
    114.18700000

    Then;
    I woould like to see the following figures (Notice, 114.37000000 is missing)
    114.18700000
    114.18700000
    114.18800000
    114.18750000
    114.18700000

    so we are taking 114.18750000 as the starting point and then go half either way. Hope you know what I mean
    [/quote]
    This sample somehow contradicts what I have understood from your first question. I cannot see the differences between the sample data and what you would like to see. From your first question I would have answered something like this:DECLARE
    @t TABLE (price decimal(8,4))
    INSERT INTO @t SELECT 100.1875
    UNION ALL SELECT 100.1870
    UNION ALL SELECT 98.390
    UNION ALL SELECT 98.395
    UNION ALL SELECT 99.000
    UNION ALL SELECT 100.1880;
    WITH myCte (price, rk)
    AS (SELECT price, RANK() OVER (PARTITION BY CAST(price * 100 AS int) ORDER BY price) AS rk
    FROM @t)

    SELECT *
    FROM myCte
    WHERE rk = 1price rk
    --------------------------------------- --------------------
    98.3900 1
    99.0000 1
    100.1870 1(
    3 row(s) affected)

Share This Page