Distinct Select Problem

[faiyth]

I am currently trying to display a list of emergencies that have happened. So that the user can either send one up as an alert, unalert it or edit it. For legal purposes they cannot delete any of the emergencies they enter.

Here's my data:
isemgid - emergency - isemgevent
1000 - FALSE - Emergency1
1001 - FALSE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3

I have two pages. alert.jsp and home.jsp.

home.jsp looks at the first field (1000) in the table and displays isemgevent if emergency is TRUE. (it only reads the first field as I only want one emergency displaying at a time).

alert.jsp is a list of all the emergencies available to choose from. When the user clicks on say, emergency2, it takes all of emergency2's data and moves it into the first field (1000) and sets emergency to TRUE. This then alerts the homepage to display this emergency instead of whatever was in 1000 before.
My problem is that 1000 and whatever field was just sent as an alert are the same except for their isemgid number. I only want to display the distinct fields. I can't delete the duplicate because if the user was to make emergency3 the alert, emergency2's information would be lost forever, and they need to be able to switch back.

This is what I want to display:
isemgid - emergency - isemgevent
1000 - FALSE - Emergency1
1002 - FALSE - Emergency2
1003 - FALSE - Emergency3

See how 1001 is missing? Now say they alert 1002 (emergency2) I want it to look like this:

isemgid - emergency - isemgevent
1000 - FALSE - Emergency2
1001 - FALSE - Emergency1
1003 - FALSE - Emergency3

So that whatever is duplicate doesn't show.

 

[jiqjaq]

I think you're trying to say that you want only unique combinations of emergency and isemgevent. If my interpretation is right, then the following will give you the combinations, keeping the first (lowest isemgid) record for any duplicates:

SELECT MIN(isemgid) isemgid, emergency, isemgevent
FROM your_table
GROUP BY emergency, isemgevent
ORDER BY isemgid

 

[cheyney]

You could also use a UNION statement to combine 2 result sets

like this:

select * from

where isemgid = 1000
UNION
select * from

where isemgid <> 1001
order by isemgid

and dynamically change 1001 to whatever your currently selected emergency is