CODEWITHSUNDEEP
mysqlsqlselectsql-order-bymax
Affan
Affan

Reputation: 1140

How to get Max of ID without use of MAX()

I want to get the maximum id from table with ought use of any function like max or else.I need simple pure query.Any one help me to solve this problem . We simply write

select max(id) from table

but i don't want to use max()

Upvotes: 2

Views: 874

Answers (6)

Amr Elsayed
Amr Elsayed

Reputation: 1

Suggestion: the MAX id not the best indicator because some times the latest records in the table may be has been deleted.

If you need the MAX id for operations related for next insertion data in this table you must get the auto increment value like below sql statement

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = queue AND TABLE_SCHEMA = Database();

Upvotes: 0

Z.W.Huang
Z.W.Huang

Reputation: 334

I added 0 to solve the problem,

select max(id + 0) from table

Upvotes: 0

varun
varun

Reputation: 1523

ORDER BY with LIMIT will do the job for u just fine

 SELECT id FROM table 
 ORDER BY id DESC LIMIT 1;

But as you asked the question from interview's point of view , they may even ask you to do the same without using LIMIT , TOP or max() . In thay case you should go with subquery approach . Here' s how u should do it : 

  SELECT id FROM table 
  WHERE  id >=  ALL
 (SELECT id FROM table)

In this query an id is matched with all the id's in the table and it will be printed only if the value is greater than or equal to all the id's in the table. Only the max will satisfy the condition.

Upvotes: 1

Chilipepper
Chilipepper

Reputation: 182

SELECT id FROM table ORDER BY id DESC LIMIT 1

This should do it

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use ORDER BY clause with LIMIT to fetch latest ID of table

Try this: 

SELECT id 
FROM table
ORDER BY id DESC 
LIMIT 1;

Upvotes: 0

Barmar
Barmar

Reputation: 780889

Use ORDER BY and LIMIT

SELECT id
FROM table
ORDER BY id DESC
LIMIT 1

Upvotes: 4

Related Questions