How to get the last ID in a certain range in MySQL?
Moderator: General Moderators
How to get the last ID in a certain range in MySQL?
Following scenario:
I want one database table to include customers, webmasters and agents.
As webmasters and agents are limited I want them in a certain range.
e.g. Webmasters have id numbers from 0 to 10000, Agents from 10001 - 20000 and customers > 20000
The id is autoincremented by customer so it is beyond 20000.
To add a new webmaster or agent I need to determine the highest number in their ranges.
Ok I can read everything in the range and check the last number but this could mean reading thousands of entries and this might take some time.
Is there a way where I can limit the lines that I have to read?
I want one database table to include customers, webmasters and agents.
As webmasters and agents are limited I want them in a certain range.
e.g. Webmasters have id numbers from 0 to 10000, Agents from 10001 - 20000 and customers > 20000
The id is autoincremented by customer so it is beyond 20000.
To add a new webmaster or agent I need to determine the highest number in their ranges.
Ok I can read everything in the range and check the last number but this could mean reading thousands of entries and this might take some time.
Is there a way where I can limit the lines that I have to read?
Last edited by AGISB on Thu Sep 23, 2004 6:36 am, edited 1 time in total.
SELECT LAST_INSERT_ID();
I believe if you are using MYSQL then you can use
or if you have just entered in a new value you can use Last_insert_id
Code: Select all
SELECT MAX( webMasterId )
FROM webMastersWould not work as it always gives me the number of the customer as it is higher than the last inserted webmaster
To clear this up: I want webmasters, agents and customers in the same table. This hast to do with authentication and other issues with referral codes etc. . If I have it in seperate tables it is a pain in the ass to make sure a webmaster id is not equal to any customer or agent id.
To clear this up: I want webmasters, agents and customers in the same table. This hast to do with authentication and other issues with referral codes etc. . If I have it in seperate tables it is a pain in the ass to make sure a webmaster id is not equal to any customer or agent id.
For example
Code: Select all
SELECT MAX(user_id)
FROM users
WHERE user_id BETWEEN 1000 AND 2000try
Code: Select all
SELECT MAX( userId )
FROM users WHERE idType='Webmasters'Thanks timvw:
Unfortunately:
and my solution I had till now
Shows me that MySQL has to examine all lines in the range.
At least it is shorter
Unfortunately:
Code: Select all
EXPLAIN SELECT MAX(Customer_ID) FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000Code: Select all
EXPLAIN SELECT Customer_ID FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000 ORDER BY Customer_ID DESC LIMIT 1At least it is shorter
You could add index over Customer_ID field. That would dramatically increase query speed because it could get data directly from index, not accessing table at all.AGISB wrote: Unfortunately:
and my solution I had till nowCode: Select all
EXPLAIN SELECT MAX(Customer_ID) FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000
Shows me that MySQL has to examine all lines in the range.Code: Select all
EXPLAIN SELECT Customer_ID FROM Customer WHERE Customer_ID BETWEEN 10000 AND 20000 ORDER BY Customer_ID DESC LIMIT 1
At least it is shorter