Picking one row out of many?

MattR · #1 (**permalink**) 06-24-03, 16:17

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?

MattR · #2 (**permalink**) 06-24-03, 16:30

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.

dbmadcap · #3 (**permalink**) 06-24-03, 17:23

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'
/

MattR · #4 (**permalink**) 06-25-03, 08:33

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

dbmadcap · #5 (**permalink**) 06-25-03, 09:39

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 ???

Quote:

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

MattR · #6 (**permalink**) 06-25-03, 10:04

Ok

Anis · #7 (**permalink**) 06-30-03, 05:04

Can't you use pref <> 'N' instead of pref = 'Y' ?

Quote:

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.

aruneeshsalhotr · #8 (**permalink**) 07-10-03, 19:19

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

Internet Services

Web Development

Digital Marketing

Consumer Tech