I got a huge table which store horse information, the
table look like this:


Date Horse Weight Distance Point
20030529 B 115 1800 99
20030601 A 130 1600 95
20030601 B 121 1200 100
20030605 A 115 1200 100
20030609 B 111 1600 101
20030610 A 105 1800 102

I want to search a pattern about if a horse got 100 point
and NEXT race is with a LIGHTER weight, what the point it
will gain?

I tried hours with Cursors, but nothing I can get, can
anyone show me some example code? I am a SQL newbie

Hi

If you as a question then it is always better to post DDL (create table
statements) and example data (as Insert Statements), with an example of the
required output. This way there will be no ambiguities in the question and
it makes it easier for anyone replying to check their answer.

CREATE TABLE HorseInfo ( [Date] DateTime, Horse Char(1), Weight INT,
Distance INT , Point int )

INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030529','B' , 115, 1800, 99 )
INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030601','A', 130, 1600, 95 )
INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030601','B', 121, 1200, 100 )
INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030605','A', 115, 1200, 100 )
INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030609','B', 111, 1600, 101 )
INSERT INTO HorseInfo ( [Date], Horse, Weight, Distance, Point ) values (
'20030610','A', 105, 1800, 102 )

The following will give a solution with the data you have provided, but I
suspect that the data is not truely representative.

SELECT I.[Date], I.Horse, I.Weight, I.Distance, I.Point
FROM HorseInfo I
JOIN ( SELECT [Date], Horse, Weight, Distance, Point
FROM HorseInfo H
WHERE H.Point = 100 ) O
ON I.date > O.date
AND I.Weight < O.Weight
AND I.Horse = O.Horse

John

[quoted text, click to view]