Query Help - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

itsfiziks
Starting Member

1 Post

Posted - 2011-06-22 : 11:36:12

I have a table that looks something like:

Observable1 Observable2 Observable3
1 2 3
2 2 2
3 2 4

I am given 3 numbers: 2, 2, 2. I want to make a query that will search through the rows to find the row with the minimum sum of absolute values.

So for each row the sum of the absolute values is ABS(2-Observable1) + ABS(2-Observable2) + ABS(2-Observable3). The second row would obviously have the least.

Any help is greatly appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 11:53:06

[code]SELECT TOP 1
*
FROM
YourTable
ORDER BY
ABS(2-Observable1) + ABS(2-Observable2) + ABS(2-Observable3) asc[/code]

super_lucky_dog
Starting Member

7 Posts

Posted - 2011-06-22 : 21:38:19

USE [sqlteam_learn]
GO

---ddl definition
drop table [dbo].[Observable];
CREATE TABLE [dbo].[Observable](
[Observable1] [int] NOT NULL,
[Observable2] [int] NOT NULL,
[Observable3] [int] NOT NULL
) ON [PRIMARY]

GO
--insert test data
insert into [Observable] values(1,2,3);
insert into [Observable] values(2,2,2);
insert into [Observable] values(3,2,4);

--hard code
select top 1
ABS(Observable1-2)+ABS(Observable2-2)+ABS(Observable3-2) sum,
[Observable1],[Observable2],[Observable3]
from [Observable]
order by ABS(Observable1-2)+ABS(Observable2-2)+ABS(Observable3-2)

--you can change values
create table #values
(
value1 int not null,
value2 int not null,
value3 int not null,
)

insert into #values values(2,2,2);

select
top 1
ABS(v.value1-o.Observable1)+ABS(v.value3-o.Observable3)+ABS(v.value3-o.Observable3) sum,
[Observable1],[Observable2],[Observable3]
from
#values v
cross join
[Observable] o
order by ABS(v.value1-o.Observable1)+ABS(v.value3-o.Observable3)+ABS(v.value3-o.Observable3) asc

v-jinxli

super_lucky_dog

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-06-22 : 23:20:45

People cannot read your mind, so post your code and clear specs if you really want help. Please post real DDL and not narrative or your own personal programming language. Learn to use ISO-11179 rules for the data element names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. Please tell us what SQL product and release you are using. Tell us if you can change the DDL or if you are stuck with it.

Now, buy any book on RDBMS and read the chapter on FIRST NORMAL FORM (1NF). You design is wrong. No key, repeating groups; Here is a normalized table:

CREATE TABLE Experiments
(experiment_nbr INTEGER NOT NULL,
observation_nbr INTEGER NOT NULL
CHECK (observation_nbr IN (1, 2, 3)),
PRIMARY KEY (experiment_nbr, observation_nbr),
observation_score INTEGER NOT NULL);

This query is pretty easy now; I did a check to see if you have all three observations.

WITH Experiment_Summary (experiment_nbr, score_tot)
AS
(SELECT experiment_nbr, SUM(ABS(2-observation_score))
FROM Experiments
GROUP BY experiment_nbr
HAVING COUNT(*) = 3))

SELECT X1.experiment_nbr, X1.score_tot AS score_tot_min
FROM Experiment_Summary AS X1
WHERE X1.score_tot
= (SELECT MIN (X2.score_tot)
FROM Experiment_Summary AS X2);
I thunk you will find that other queries will be faster and easier than your original table.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources