Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chrisg229
Starting Member
12 Posts |
 Posted - 2007-09-17 : 18:38:11
|
| I have several records with duplicate values and I need to aggregate them with a function similar to MIN(number). MIN would be perfect except that I want to use the value of the number closest to 0, using MIN often gives me numbers less than 0 even when 0 is available.Example logicIf one of the numbers is 0 use that (exclude all other dupes)else if one of the numbers is -1 use that (exclude all other dupes)else if one of the numbers is 1 use that (exclude all other dupes)else if one of the numbers is -2 use that (exclude all other dupes)else if one of the numbers is 2 use that (exclude all other dupes)etc...Is there a function similar to MIN that can do this or else does anyone know of some SQL case/iif logic to accomplish this task?Thanks in advance.Chris |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-17 : 18:45:28
|
SELECT <col list> FROM (SELECT <col list>, ROW_NUMBER() OVER (PARTITION BY PkCol ORDER BY ABS(Number)) AS RecID FROM Table1) AS d WHERE RecID = 1
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
chrisg229
Starting Member
12 Posts |
Posted - 2007-09-17 : 18:55:30
|
| That did it. Thanks Peso! |
|
|
|
|
|
|
Number Closest to Zero