Complex Sql Query

[Mark]

I'm not sure if this is possible but what i want to do is as follows:
I need to

Select all values in a table that are smaller then the preceding one
timewise or ID wise. Which ever.
I have three columns. ID, timestamp and value.

I'm using SQL server 2000. Is it possible to do this withou transact
sql?

Thanks

Mark

[David Portas]

It helps others understand your requirements better if you post DDL and
sample INSERT statements. TIMESTAMP is a reserved word for the Timestamp
datatype but I will assume that what you mean in this case is a DATETIME
column. I've also assumed that the DATETIME column is unique.

When you say "without transact SQL" do you mean you want to avoid using
proprietary features?

Try this:

CREATE TABLE SomeTable (id INTEGER PRIMARY KEY, ts DATETIME NOT NULL UNIQUE,
value INTEGER NOT NULL)

INSERT INTO SomeTable (id,ts,value) VALUES (1,'2004-01-01T00:00:00',100)
INSERT INTO SomeTable (id,ts,value) VALUES (2,'2004-01-01T00:01:00',90)
INSERT INTO SomeTable (id,ts,value) VALUES (3,'2004-01-01T00:02:00',95)
INSERT INTO SomeTable (id,ts,value) VALUES (4,'2004-01-01T00:03:00',94)

SELECT T.id, T.ts, T.value
FROM SomeTable AS T
JOIN
(SELECT S1.value, MIN(S2.ts) AS ts
FROM SomeTable AS S1
JOIN SomeTable AS S2
ON S1.ts < S2.ts
GROUP BY S1.id, S1.value) AS S
ON S.ts = T.ts AND S.value > T.value

--
David Portas
SQL Server MVP
--

[Mark]

That Actually worked! Except it was way to slow. Currently we have
about 5000 values and it took about 7-10 seconds to run that query.
That's definately unacceptable. Any better ways to go about it?

I'm still impressed at how that worked. Do you know of any site that
could explain to me how that worked. I guess the part i didn't really
understand was the join with the '<' operator.

[David Portas]

This could be a little quicker:

SELECT T.id, T.ts, T.value
FROM SomeTable AS T
WHERE value <
(SELECT value
FROM Sometable
WHERE ts =
(SELECT MAX(ts)
FROM Sometable
WHERE ts<T.ts))

could explain to me how that worked. I guess the part i didn't really
understand was the join with the '<' operator.

The purpose of the self-join was just to join each row to the previous ones
(S1.ts < S2.ts) but in actual fact I was trying to be too clever!

--
David Portas
SQL Server MVP
--

[Mark]

Wow it worked great and fast. Thanks a lot. And i thought i knew all
about SQL queries

Mark

[--CELKO--]

DROP TABLE Foobar;
CREATE TABLE Foobar
(clock_in DATETIME NOT NULL PRIMARY KEY,
val INTEGER NOT NULL);

INSERT INTO Foobar VALUES ('2004-01-01', 100)
INSERT INTO Foobar VALUES ('2004-01-02', 90)
INSERT INTO Foobar VALUES ('2004-01-03', 95)
INSERT INTO Foobar VALUES ('2004-01-04', 94)
INSERT INTO Foobar VALUES ('2004-01-05', 101)
INSERT INTO Foobar VALUES ('2004-01-06', 97)
INSERT INTO Foobar VALUES ('2004-01-07', 95)
INSERT INTO Foobar VALUES ('2004-01-08', 94)

SELECT F1.clock_in, F1.val,
SUM (CASE WHEN F2.val < F1.val THEN 1 ELSE 0 END) AS future_lesser_vals
FROM Foobar AS F1, Foobar AS F2
WHERE F2.clock_in > F1.clock_in
GROUP BY F1.clock_in, F1.val;