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

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".

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.

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

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

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)

[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 thisECLARE @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)