Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: Picking one row out of many?

    Hi,

    I have a table that lists a particular user's email addresses in the system:
    Email( User_ID, Type, Address, Preferred )

    The user can, if they have more than one email of a given type, choose one as their primary.

    For example:
    { Matt, Home, Matt@Home.com, N }
    { Matt, Home, Matt1@Home.com, Y }

    So, if I wanted to get all users' home emails I'd simply select where type = home and pref = 'y'.

    However, if there is only *one* email of a type in there, then the pref code can be anything (not always 'Y').

    That means that I need to get a single email for each user for the home type *but* if there is more than one, choose the one with preferred = 'Y'.

    I've been searching through the data and it seems to hold that if there is more than one, there will always be one marked Y. It is not guaranteed that it will be marked 'Y' for a single email.

    Any help?
    Thanks,

    Matt

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    My current SQL is:
    Code:
     SELECT address
       FROM email,
          ( SELECT COUNT( * ),
    	           user_id
    	      FROM address
    		 WHERE type = 'HOME'
     		 GROUP BY user_id
      	    HAVING COUNT( * ) > 1 ) multiple
     WHERE user_id = multiple.user_id
       AND type    = 'HOME'
       AND pref    = 'Y' 
    UNION
     SELECT address
       FROM email,
          ( SELECT COUNT( * ),
    	           user_id
    	      FROM address
    		 WHERE type = 'HOME'
     		 GROUP BY user_id
      	    HAVING COUNT( * ) = 1 ) single
     WHERE user_id = single.user_id
       AND type    = 'HOME'
    That will give me all primary emails when a user has multiples, then I union that with the same type of query except having count( * ) = 1 and dropping the pref = 'Y' part, but it seems very, very inefficent.
    Thanks,

    Matt

  3. #3
    Join Date
    May 2003
    Posts
    87
    Does this sql get you want you want ???

    Code:
    select 
    decode(a.pref_ctr,0, (select address from where user_id = a.user_id and type = 'HOME'), (select address from where user_id = a.user_id and type = 'HOME' and preferred = 'Y'))
    from
    ( select user_id, sum(decode(preferred,'Y',1,0)) pref_ctr, count(*) tot_ctr
      from email
      where type = 'HOME'
      group by user_id
    ) a
    where type = 'HOME'
    /

  4. #4
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Ooh it seems like it does. I really have got to get the hang of the CASE and DECODE statements.
    Thanks,

    Matt

  5. #5
    Join Date
    May 2003
    Posts
    87
    cool ! :-)

    Yes, CASE & DECODE are some unique functions that Oracle has -- a boon to developers

    Now, can you please reply to my question on php/perl ???

    Originally posted by MattR
    Ooh it seems like it does. I really have got to get the hang of the CASE and DECODE statements.

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    Ok
    Thanks,

    Matt

  7. #7
    Join Date
    Jun 2003
    Posts
    2
    Can't you use pref <> 'N' instead of pref = 'Y' ?


    Originally posted by MattR
    My current SQL is:
    Code:
     SELECT address
       FROM email,
          ( SELECT COUNT( * ),
    	           user_id
    	      FROM address
    		 WHERE type = 'HOME'
     		 GROUP BY user_id
      	    HAVING COUNT( * ) > 1 ) multiple
     WHERE user_id = multiple.user_id
       AND type    = 'HOME'
       AND pref    = 'Y' 
    UNION
     SELECT address
       FROM email,
          ( SELECT COUNT( * ),
    	           user_id
    	      FROM address
    		 WHERE type = 'HOME'
     		 GROUP BY user_id
      	    HAVING COUNT( * ) = 1 ) single
     WHERE user_id = single.user_id
       AND type    = 'HOME'
    That will give me all primary emails when a user has multiples, then I union that with the same type of query except having count( * ) = 1 and dropping the pref = 'Y' part, but it seems very, very inefficent.

  8. #8
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Unique Email address

    Hi,
    I saw one posting in the solution with decode function being used, and I am going to present a solution on the same lines, as union is bad for such cases.

    select email from (Select email, homeid from ordertable order by homeid desc) where decode(homeid, Y,Y,N,Y)=Y

    This should work in your case. I used a similar query on my table and it works fine for me.
    Good question though.

    Aruneesh

Posting Permissions

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