Re: "Lost in Select"

[Carlton Conley <cconley@CDG-INC.COM>]

does it matter if you get the last or the first, you just want one, and the
max will sort through and find the highest value and return one row.....if
you have a date on the table you could actually get the last row they
entered using the max(date)

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Deborah DeCarvalho
Sent: Thursday, May 11, 2000 2:59 PM
To: SQR-USERS@list.iex.net
Subject: Re: "Lost in Select"


I am getting some great suggestions, but I may have left something out -
because of the way we assign member_id's they are not necessarily
sequential - the member_id is based on the "cookie" we place with the user -
and yes, teh cookie number is random numbers

-----Original Message-----
From: Discussion of SQR, Brio Technology's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Pragya Chandra
Sent: Thursday, May 11, 2000 3:35 PM
To: SQR-USERS@list.iex.net
Subject: Re: "Lost in Select"


what you can do...and I am not saying that it is the most efficient way of
doing it is select distinct email addresses and then in the second select
get the member name...I'll try to use the code you gave for an example:

        select distinct a.email
               do second-select
        from member a, group_member b
        where  a.member_id = b.member_id
            and b.group_id     ='41'
            and b.member_since = (select max(b1.member_since) from
group_member b1 )<--you need to join the keys in the subselect

begin-procedure second-select

begin-select
a1.member_name
from member a1
where a1.email = &a.email
 and rownum = 1
end-select

end-procedure second-select

rownum is what you use in Oracle to get one row of data only..(if you only
want one name)...check for your own database

> -----Original Message-----
> From: Deborah DeCarvalho [SMTP:ddecarvalho@IBELONG.COM]
> Sent: Thursday, May 11, 2000 2:27 PM
> To:   SQR-USERS@list.iex.net
> Subject:      "Lost in Select"
>
> Afternoon all -
> I seem to be having a problem getting a very minor select to work and I
> have
> no idea what I'm  missing - Now, I have been staring at this for well over
> 3
> hours so I'm thinking I am either missing something so dumb that I'll end
> up
> looking like an a**  or I'll leave immediately on vacation, but here
> goes..
>
> I have 2 tables I need to query for a sweepstakes promotion. ( I said it
> was
> minor !)
> Since we have multiple sweepstakes on the web portals, I need my SQR to
> select based on the group and on the date they registered on the portal -
>
> I'm all set with the SQR prompting for the group and the dates, but here's
> the problem, our code produces a "member_id" when a person registers on
> the
> portal - They MUST use a unique member_name when registering otherwise,
> they're told to try again - (member_name is a key field)
> But, you can register as many times as you WANT as long as you use a
> different member_name each time. Thats where my problem starts - I have
> multiple people with the same email address but different user names - I
> only want unique email names.
> I was using this
>
> select distinct(a.First_Name||','||a.Last_name||','||a.email) from member
> a,
> group_member b
>     where a.member_id = b.member_id
>     and b.group_id='41'
>     and a.email not like '%ibelong%'
>
> But that doesn't work - I get duplicate email addresses
> I also tried
>
>      select a.email,a.member_name, b.member_since from member a,
> group_member b where
>      a.member_id=b.member_id
>      and b.group_id='41' and
>      b.member_since = (select max(b.member_since) from group_member b )
>
> This HAS to be sooo simple, that I'll take my SQR manuals and throw them
> out
> the window right?
> Any suggestions??? (BTW, the group number is there for testing - it isn't
> actually in the select - There is a prompt for that)
>
> TIA
>
> Deborah deCarvalho
> Operations/Technology
> IBelong Networks
> www.ibelong.com
> 781.672.8349
> mailto:ddecarvalho@ibelong.com