Exclude Records from a table based on a condition

[Ann VK]

Can someone help me to figure out a query for this requirement? I have a table with the follwoign columns:

ID Agency Value
1 A 1000
1 B 2000
1 C 3000
2 A 1100
2 C 3100
3 B 2200
3 C 3200

Now i want to be able to retrieve so that if for an ID the Agency A and B are pr esent then i want to take the row of A else if onyl A is present then A else if only B is present then B. Can i do this in one query?

[Rabbit]

I have no idea what you're trying to do. What would be the results of said query from the sample data? What query do you have so far? What is that query doing wrong? What is it doing right?

[DroidSlave300]

you can try SELECT TOP 1 making sure that you order by AGENCY

[Ann VK]

I have no idea what you're trying to do. What would be the results of said query from the sample data? What query do you have so far? What is that query doing wrong? What is it doing right?

I am really sorry i was plannign to put the example of how i want hte putput to be but i forgot. Her eis how the output should be:
ID Agency Value
1 A 1000
1 C 3000
2 A 1100
2 C 3100
3 B 2200
3 C 3200
So in short if for an ID both Agency A and B are present then i want only IDs that have Agency = A. But in case if the IDs has only Agency B then i want that records. Hoep this make sense.

[Rabbit]

If you only want A or B, why does your sample result show C?

[Ann VK]

I will have C and D as Agency. THe user is not worried about that. Only the A and B can be like duplicate records which they dont want to see together for the same IDs.

[Rabbit]

You could use the row number function to number them ordering by the agency and partitioning by the id. Then you can filter just for the ones where the row number is 1 or the agency is not a or b.

[Ann VK]

You could use the row number function to number them ordering by the agency and partitioning by the id. Then you can filter just for the ones where the row number is 1 or the agency is not a or b.

Thanks for the response. This worked.

[Rabbit]

No problem. Good luck with the rest of your project.

On a side note, the OR may possibly make it run slowly, if it does, you can use a union instead.