My intial post didn't seem to come out. Let's try again,
Hi All,
I have another SQL question, and this one is going to be difficult to
explain.
I have a table like this
rowid name changed
1 a 1
1 b 0
1 c 1
1 d 0
2 ....
2 ...
2 ....
I want to be able to find the next rowid in my table which matches my
criteria (my table has thousands, even hundreds of thousands of rows)
The criteria is the trouble you see. I have the notion when querying
against the name field of 'required' values and 'optional' values. So
for example if I said give me the first rowid which has a required
value 'a' and an optional value 'z' it would still give me the first
rowid (1) because z was an optional value.
But if I turned it around and said the required value was 'z' and
optional was 'a' it would not return rowid 1 because the first set of
rowids there do not have a value z.
That first requirement can be achieved with a query like this,
select * from table where rowid in
(select top (1) RowId from table where Name in ('required1',
'required2')
group by rowid having Count(Rowid) >= 2)
and Name in ('required1', 'required2','op tional1','optio nal2')
So the inner query there forces the number of rows to be above 2 there
which would correspond to a match of 2 required values.
That works fine, but there is one more requirement which I have not
been able to handle. That requirement is to do with the changed column
in the table above. I need to be able to make sure that at least one
of the required or optional values has a 1 in that column.
I can make that happen with another subquery, but the query then
becomes too inefficient.
Please help, even if you are unsure, please help anyway.
Thanks.
Hi All,
I have another SQL question, and this one is going to be difficult to
explain.
I have a table like this
rowid name changed
1 a 1
1 b 0
1 c 1
1 d 0
2 ....
2 ...
2 ....
I want to be able to find the next rowid in my table which matches my
criteria (my table has thousands, even hundreds of thousands of rows)
The criteria is the trouble you see. I have the notion when querying
against the name field of 'required' values and 'optional' values. So
for example if I said give me the first rowid which has a required
value 'a' and an optional value 'z' it would still give me the first
rowid (1) because z was an optional value.
But if I turned it around and said the required value was 'z' and
optional was 'a' it would not return rowid 1 because the first set of
rowids there do not have a value z.
That first requirement can be achieved with a query like this,
select * from table where rowid in
(select top (1) RowId from table where Name in ('required1',
'required2')
group by rowid having Count(Rowid) >= 2)
and Name in ('required1', 'required2','op tional1','optio nal2')
So the inner query there forces the number of rows to be above 2 there
which would correspond to a match of 2 required values.
That works fine, but there is one more requirement which I have not
been able to handle. That requirement is to do with the changed column
in the table above. I need to be able to make sure that at least one
of the required or optional values has a 1 in that column.
I can make that happen with another subquery, but the query then
becomes too inefficient.
Please help, even if you are unsure, please help anyway.
Thanks.