Returning distinct based on column value 1

[monkle]

I am trying to generate an SQL query that returns a DISTINCT row from a single table based on certain column values.

For example:

Table test:
col1 col2 col3
a b c row 1
a b c row 2
a b d row 3
w x y row 4
k b m row 5

I need to be able to generate a script that will return distinct on the first column, and also only give me the rows where the second column value is b.

For example, with the table given, it needs to return two rows, one of any of the first three rows (I don't care which, I only care that it leaves two of them out), and row 5, but not row 4.

I'm going to have to write a script that generates the query based on user input. I have managed to write queries that will only return distinct on the first column, but when I add additional required columns to the query it gives me multiple rows where column 1 are the same.

I hope that this makes sense. I have tried to use distinct, and also EXISTS and EXISTS IN, but if it's possible to get the query functioning correctly using these, I have not found the correct syntax. Any input would be greatly appreciated. The actual final query is a little more complex that this example, as additional columns are added to the equation, but I think that this example conveys the question.

 

[vongrunt]

Any primary/unique key on that table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[monkle]

yes. Using the above example, let's say it's column 4 (It is not the column that I am trying to return distinct on)

 

[vongrunt]

In that case...

select A.*
from myTable A
inner join
(	select min(col4) as col4
	from myTable
	where col2 = 'b'
	group by col1, col2, col3
) B on A.col4 = B.col4

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[monkle]

vongrunt:
Thank you very much for your help, I have no idea how much time you saved me. You can see what I was working on by going to

http://www.movies4wholesale.com/test/alphalisting.php?category=4&go=1&ppstart=$0.50&ppend=$0.50

. The listing was by sku, and consequently it was returning multiple items for the same title and format, which was entirely unacceptable. Thank you again.

 

[monkle]

I'm sorry, I posted an incorrect url to the webpage if you wanted to see where the query was being used. The correct url is

http://www.movies4wholesale.com/alphalisting.php?category=4&go=1&ppstart=$0.50&ppend=$0.50