July 5, 2007 at 2:16 am
I have a table with 3 columns - ID, Sample#, MaxValue with the following data:
ID Sample# MaxValue
BBH 1 1
BBH 2 2
BBH 3 1
BBH 4 2
KDT 1 2
KDT 2 2
KDT 3 3
KDT 4 2
KDT 5 4
PER 1 1
PER 2 2
PER 3 1
PER 4 1
PER 5 1
PER 6 1
PER 7 2
I want to write one select statement that returns only those rows where the MaxValue has increased since the previous sample# for a particular ID. So for the sample data, the select statement should return the following rows:
ID Sample# MaxValue
BBH 2 2
BBH 4 2
KDT 3 3
KDT 5 4
PER 2 2
PER 7 2
Any ideas?
July 5, 2007 at 2:41 am
for your problem space you can use this solution but you might have to adapt for other problem spaces e.g. where sample is not a number but some characters etc
SELECT
DISTINCT A.id,A.[sample],A.maxvalue
FROM
(
SELECT id,[sample],maxvalue
FROM
Samples
) A
INNER
JOIN
(
SELECT
id,[sample],maxvalue
FROM
Samples
)
B ON A.id = B.id AND A.maxvalue > B.maxvalue AND B.[sample] < A.[sample]
July 5, 2007 at 3:34 am
Thanks bledu, the solution almost works (my fault - I didn't give a very good representation of the data, or maybe a clear explanation). It should look like this:
ID Sample# MaxValue
BBH 1 1
BBH 2 2
BBH 3 1
BBH 4 2
BBH 5 2
KDT 1 2
KDT 2 2
KDT 3 3
KDT 4 3
KDT 5 4
PER 1 1
PER 2 2
PER 3 2
PER 4 2
PER 5 2
PER 6 1
PER 7 2
The query should return a row only if the MaxValue is greater than the previous MaxValue - not all subsequent MaxValues greater than the lowest. ie the following rows:
BBH 2 2
BBH 4 2
KDT 3 3
KDT 5 4
PER 2 2
PER 7 2
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply