I keep a list of email addresses of individuals that request
information on our dogs. I have a table for the address.
I have the person's id in col 1, a sequence number in col2 (some
individuals request that info be sent to a preferred address or to
multiple addresses) and the email addy in col3.

What I really want is to have the sequence number auto increment when
I add an address, but it needs to be within that person's id (numeric)
eg:
ID sequence email
01 01 yada@yada.com
01 02 yada@another.com

In order to do this manually I need to find the current highest
sequence so I can increment it. In my prior DB I would just to a LT
search with the ID and 99 as the sequence, but I don't know how to do
this in mysql (from PHP).

I appreciate your assistance

bill

On Jun 17, 12:46 pm, will...@TechServSys.com wrote:
> I keep a list of email addresses of individuals that request
> information on our dogs. I have a table for the address.
> I have the person's id in col 1, a sequence number in col2 (some
> individuals request that info be sent to a preferred address or to
> multiple addresses) and the email addy in col3.
>
> What I really want is to have the sequence number auto increment when
> I add an address, but it needs to be within that person's id (numeric)
> eg:
> ID sequence email
> 01 01 y...@yada.com
> 01 02 y...@another.com
>
> In order to do this manually I need to find the current highest
> sequence so I can increment it. In my prior DB I would just to a LT
> search with the ID and 99 as the sequence, but I don't know how to do
> this in mysql (from PHP).
>
> I appreciate your assistance
>
> bill

Because you're never likely to have more than 2 or 3 addresses for a
given contact, one could advocate a non-normalized approach as
follows:

client_adress
client_id* address_id* alt_address_id1 alt_address_id2
1 102 105 106
2 103 104 NULL
3 107 NULL NULL

client(client_id*,f_name,l_name,mobile)

address(address_id*,street,city,code)

On Jun 17, 9:08 am, strawberry <zac.ca...@gmail.com> wrote:

> Because you're never likely to have more than 2 or 3 addresses for a
> given contact, one could advocate a non-normalized approach as
> follows:
>
> client_adress
> client_id* address_id* alt_address_id1 alt_address_id2
> 1 102 105 106
> 2 103 104 NULL
> 3 107 NULL NULL
>
> client(client_id*,f_name,l_name,mobile)
>
> address(address_id*,street,city,code)

This makes it much harder to find the client_id by the email address,
or is there a sql query that would permit you to find a given address
regardless of being in address_id, alt_address_id or alt_address_id2 ?

bill

strawberry wrote:
> On Jun 17, 12:46 pm, will...@TechServSys.com wrote:
>> I keep a list of email addresses of individuals that request
>> information on our dogs. I have a table for the address.
>> I have the person's id in col 1, a sequence number in col2 (some
>> individuals request that info be sent to a preferred address or to
>> multiple addresses) and the email addy in col3.
>>
>> What I really want is to have the sequence number auto increment when
>> I add an address, but it needs to be within that person's id (numeric)
>> eg:
>> ID sequence email
>> 01 01 y...@yada.com
>> 01 02 y...@another.com
>>
>> In order to do this manually I need to find the current highest
>> sequence so I can increment it. In my prior DB I would just to a LT
>> search with the ID and 99 as the sequence, but I don't know how to do
>> this in mysql (from PHP).
>>
>> I appreciate your assistance
>>
>> bill
>
> Because you're never likely to have more than 2 or 3 addresses for a
> given contact, one could advocate a non-normalized approach as
> follows:
>

Go wash your mouth out with soap! :-)


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> strawberry wrote:
> > On Jun 17, 12:46 pm, will...@TechServSys.com wrote:
> >> I keep a list of email addresses of individuals that request
> >> information on our dogs. I have a table for the address.
> >> I have the person's id in col 1, a sequence number in col2 (some
> >> individuals request that info be sent to a preferred address or to
> >> multiple addresses) and the email addy in col3.
> >>
> >> What I really want is to have the sequence number auto increment when
> >> I add an address, but it needs to be within that person's id (numeric)
> >> eg:From:
strawberry <zac.carey@gmail.com> - change nickname


To:


Jerry Stuckle <jstuck...@attglobal.net>







(Separate multiple groups with commas)





Followup-to:

(Request replies to this message to be sent to these groups)


Add followup-to header

Cc:


Subject:


Message:



Jerry Stuckle wrote:
> strawberry wrote:
> > On Jun 17, 12:46 pm, will...@TechServSys.com wrote:
> >> I keep a list of email addresses of individuals that request
> >> information on our dogs. I have a table for the address.
> >> I have the person's id in col 1, a sequence number in col2 (some
> >> individuals request that info be sent to a preferred address or to
> >> multiple addresses) and the email addy in col3.
> >>
> >> What I really want is to have the sequence number auto increment when
> >> I add an address, but it needs to be within that person's id (numeric)
> >> eg:
> >> ID sequence email
> >> 01 01 y...@yada.com
> >> 01 02 y...@another.com
> >>
> >> In order to do this manually I need to find the current highest
> >> sequence so I can increment it. In my prior DB I would just to a LT
> >> search with the ID and 99 as the sequence, but I don't know how to do
> >> this in mysql (from PHP).
> >>
> >> I appreciate your assistance
> >>
> >> bill
> >
> > Because you're never likely to have more than 2 or 3 addresses for a
> > given contact, one could advocate a non-normalized approach as
> > follows:
> >
>
> Go wash your mouth out with soap! :-)
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================






Send me a copy of this message












> >> ID sequence email
> >> 01 01 y...@yada.com
> >> 01 02 y...@another.com
> >>
> >> In order to do this manually I need to find the current highest
> >> sequence so I can increment it. In my prior DB I would just to a LT
> >> search with the ID and 99 as the sequence, but I don't know how to do
> >> this in mysql (from PHP).
> >>
> >> I appreciate your assistance
> >>
> >> bill
> >
> > Because you're never likely to have more than 2 or 3 addresses for a
> > given contact, one could advocate a non-normalized approach as
> > follows:
> >
>
> Go wash your mouth out with soap! :-)
>
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
o o
:-8

You're too harsh! Anyway, you can't rubbbish someone else's idea
unless you present a better one of your own (unless you're an art
critic). ;-)

will...@TechServSys.com wrote:
> I keep a list of email addresses of individuals that request
> information on our dogs. I have a table for the address.
> I have the person's id in col 1, a sequence number in col2 (some
> individuals request that info be sent to a preferred address or to
> multiple addresses) and the email addy in col3.
>
> What I really want is to have the sequence number auto increment when
> I add an address, but it needs to be within that person's id (numeric)
> eg:
> ID sequence email
> 01 01 yada@yada.com
> 01 02 yada@another.com
>
> In order to do this manually I need to find the current highest
> sequence so I can increment it. In my prior DB I would just to a LT
> search with the ID and 99 as the sequence, but I don't know how to do
> this in mysql (from PHP).
>
> I appreciate your assistance
>
> bill
Anyway, to answer the OP's original question...

SELECT t1.sequence
FROM contacts t1
LEFT JOIN contacts t2
ON t1.id = t2.id
AND t1.sequence < t2.sequence
WHERE t2.id IS NULL

will...@TechServSys.com wrote:
> I keep a list of email addresses of individuals that request
> information on our dogs. I have a table for the address.
> I have the person's id in col 1, a sequence number in col2 (some
> individuals request that info be sent to a preferred address or to
> multiple addresses) and the email addy in col3.
>
> What I really want is to have the sequence number auto increment when
> I add an address, but it needs to be within that person's id (numeric)
> eg:
> ID sequence email
> 01 01 yada@yada.com
> 01 02 yada@another.com
>
> In order to do this manually I need to find the current highest
> sequence so I can increment it. In my prior DB I would just to a LT
> search with the ID and 99 as the sequence, but I don't know how to do
> this in mysql (from PHP).
>
> I appreciate your assistance
>
> bill

- Show quoted text -
will...@TechServSys.com wrote:
> I keep a list of email addresses of individuals that request
> information on our dogs. I have a table for the address.
> I have the person's id in col 1, a sequence number in col2 (some
> individuals request that info be sent to a preferred address or to
> multiple addresses) and the email addy in col3.


> What I really want is to have the sequence number auto increment when
> I add an address, but it needs to be within that person's id
(numeric)
> eg:
> ID sequence email
> 01 01 y...@yada.com
> 01 02 y...@another.com


> In order to do this manually I need to find the current highest
> sequence so I can increment it. In my prior DB I would just to a
LT
> search with the ID and 99 as the sequence, but I don't know how to
do
> this in mysql (from PHP).


> I appreciate your assistance


> bill

Anyway, to answer the OP's original question...

SELECT t1.sequence
FROM contacts t1
LEFT JOIN contacts t2
ON t1.id = t2.id
AND t1.sequence < t2.sequence
WHERE t2.id IS NULL

or

SELECT id,max(sequence) FROM contacts GROUP BY id

or

SELECT max(sequence) FROM contacts WHERE id = 1