Re: Record Numbering - was SELECT MAX(ID) PLUS 1

Hi Lisa, At 4:40 AM -0600 28/3/01, Rubanowicz, Lisa wrote:

Can anyone help me on this one Lisa

[snip]

Here is the story. I have a small web site running from mySQL. Initially

I had it all in different tables then an ASP colleague said I should do it it one table and use SELECT MAX(sub_id) etc.

Well although MYSql is not truly a relational database your ASP colleague does not seem to know a lot about database design !! If it was me I would use extra tables for the first few levels... You can use a self referencing table (which is what you are trying to do) but IMHO the extra tables approach provides more speed and flexibility. Otherwise I can't see a reason why the query you are trying should not work.. a better and (usually) faster approach is to use another table to store the record numbers eg.. Table - record_numbers RECNUM_PKEY (Primary Key) (this holds the Table ID or Key eg "L1", "L2" etc) RECNUM_VALUE (holds the next record number) you could easily write a php function to get the next record number for any table.. This may be overkill for your project but it is very flexible . HTH Steve -- --------------------------------------------------- "Minds are like parachutes, they work best when open" Support free speech; visit http://www.efa.org.au/ Heads Together Systems Pty Ltd http://www.hts.com.au Email: hts@hts.com.au Tel: 612 9982 6767 Fax: 612 9981 3081