Professional Web Applications Themes

Finding highest value for a set of columns - MySQL

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 com 01 02 com In order to do this manually I ...

  1. #1

    Default Finding highest value for a set of columns

    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 com
    01 02 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

    william@TechServSys.com Guest

  2. #2

    Default Re: Finding highest value for a set of columns

    On Jun 17, 12:46 pm, 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)

    strawberry Guest

  3. #3

    Default Re: Finding highest value for a set of columns

    On Jun 17, 9:08 am, strawberry <com> wrote:
     

    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


    bill Guest

  4. #4

    Default Re: Finding highest value for a set of columns

    strawberry 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:
    >[/ref]

    Go wash your mouth out with soap! :-)


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

  5. #5

    Default Re: Finding highest value for a set of columns


    Jerry Stuckle wrote: [/ref][/ref]
    strawberry <com> - change nickname


    To:


    Jerry Stuckle <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: 
    > >
    > > 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:
    > >[/ref]
    >
    > Go wash your mouth out with soap! :-)
    >
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]






    Send me a copy of this message











     
    > >
    > > 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:
    > >[/ref]
    >
    > Go wash your mouth out with soap! :-)
    >
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]
    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). ;-)

    strawberry Guest

  6. #6

    Default Re: Finding highest value for a set of columns


    com wrote: 
    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

    strawberry Guest

  7. #7

    Default Re: Finding highest value for a set of columns


    com wrote: 

    - Show quoted text -
    com wrote: 

     
    (numeric) 

     
    LT 
    do 

     

     

    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

    strawberry Guest

Similar Threads

  1. Highest Distinct Value
    By mslima in forum Coldfusion Database Access
    Replies: 4
    Last Post: April 19th, 01:42 PM
  2. Columns and Inherited Datagrid...Active Schema does not support columns
    By rob thomson in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: September 4th, 03:09 PM
  3. question with Highest occurrences
    By msg_2222@yahoo.com in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: August 13th, 03:21 PM
  4. Replies: 0
    Last Post: July 2nd, 06:18 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139