CASE statement?...if then...to show only certain records?

[skijop]

I need to return only certain rows based on some logic:

(for each sets of episode nbr and mpi id, if there is a ranking of 1, then ONLY show 1) ELSE
(for each episode nbr per mpi id, if there is NO ranking of 1 then just return all rankings no matter what)

Is this case statements?? I want to return all of these columns but just the certain rows...(for this example it happens to be the first and last row)

 

[AlexCuse]

I think you want a union query. This query will have two parts

1. Get all the rows where ranking = 1
2. Get all the rows that don't have episode_nbr/mpi_id combinations with a ranking of 1

here's an example:

[COLOR=blue]select[/color] * [COLOR=green]--replace with column names
[/color][COLOR=blue]from[/color] myTable 
[COLOR=blue]where[/color] ranking = 1

union all

[COLOR=blue]select[/color] a.*
[COLOR=blue]from[/color] myTable a
[COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
    [COLOR=blue]select[/color] [COLOR=#FF00FF]distinct[/color] mpi_id, episode_nbr
	[COLOR=blue]from[/color] myTable
	[COLOR=blue]where[/color] ranking = 1
	) b
[COLOR=blue]on[/color] a.mpi_id = b.mpi_id
and a.episode_nbr = b.episode_nbr
[COLOR=blue]where[/color] b.mpi_id [COLOR=blue]is[/color] null

The subquery (aliased as b) in the second part of the union will return a list of all episode_nbr/mpi_id combinations that have a ranking of 1. By left joining to this, and taking only the rows where the subquery's mpi_id is null (aka there is not a match) you will get all rows for episode_nbr/mpi_id combinations that do not have a ranking of 1 in the table.

Post back if you have any questions.

I hope this helps,

Alex



[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom