Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1

    Unanswered: Distinct Problem

    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 - TRUE - 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 - TRUE - Emergency2
    1001 - FALSE - Emergency1
    1003 - FALSE - Emergency3

    So that whatever is duplicate doesn't show.

  2. #2
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249

    Re: Distinct Problem

    --Is this what you want?

    select yt.isemgid, yt.emergency, yt.isemgevent
    from YourTable yt
    join (
    select isemgid=max(isemgid)
    from YourTable
    group by isemgevent
    ) XXX on yt.isemgid=XXX.isemgid
    order by yt.isemgevent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •