SQL - How to find the highest number in a column

Question

Let s say I have the following data in the Customers table   nothing more   ID   FirstName   LastName ------------------------------- 20   John        Mackenzie 21   Ted         Green 22   Marcy       Nate   What sort of SELECT statement can get me the number 22  in the ID column   I need to do something like this to generate a unique ID  Sure I can let the system do this via auto-increment  but then how would I get the auto generated ID   I thought of SELECT ID FROM Customers and counting the rows returned but this seems horribly inefficient  and in this case  it will incorrectly return  3   though I need a unique ID of 23

User · Answer

If you want to just select the id use select max id  from customer   If you want to select the entire row then use a query like this   select c1   from customer c1   select max id  as max id from customer  c2  where c1 id c2 max id   c2 is an alias for the new temporary table which contains max id  Then its cross product is taken with customer table to get the entire row    Here we are writing a query in the from clause  which can often be quite useful

User · Answer

If you re not using auto-incrementing fields  you can achieve a similar result with something like the following   insert into Customers  ID  FirstName  LastName      select max ID  1   Barack    Obama  from Customers    This will ensure there s no chance of a race condition which could be caused by someone else inserting into the table between your extraction of the maximum ID and your insertion of the new record   This is using standard SQL  there are no doubt better ways to achieve it with specific DBMS  but they re not necessarily portable  something we take very seriously in our shop

User · Answer

If you are using AUTOINCREMENT  use    SELECT LAST  INSERT  ID      Assumming that you are using Mysql  http   dev mysql com doc refman 5 0 en example-auto-increment html  Postgres handles this similarly via the currval sequence name  function   Note that using MAX ID  is not safe  unless you lock the table  since it s possible  in a simplified case  to have another insert that occurs before you call MAX ID  and you lose the id of the first insert  The functions above are session based so if another session inserts you still get the ID that you inserted

User · Answer

select max id  from Customers

User · Answer

To get it at any time  you can do SELECT MAX Id  FROM Customers    In the procedure you add it in  however  you can also make use of SCOPE IDENTITY -- to get the id last added by that procedure  This is safer  because it will guarantee you get your Id--just in case others are being added to the database at the same time

User · Answer

In PHP    sql   mysql query  select id from customers order by id desc     rs   mysql fetch array  sql   if     rs      newid   1    else    newid    rs newid  1      thus  newid   23 if last record in column id was 22

User · Answer

If you re talking MS SQL  here s the most efficient way   This retrieves the current identity seed from a table based on whatever column is the identity   select IDENT CURRENT  TableName   as LastIdentity   Using MAX id  is more generic  but for example I have an table with 400 million rows that takes 2 minutes to get the MAX id    IDENT CURRENT is nearly instantaneous

User · Answer

Depends on what SQL implementation you are using  Both MySQL and SQLite  for example  have ways to get last insert id  In fact  if you re using PHP  there s even a nifty function for exactly that mysql insert id     You should probably look to use this MySQL feature instead of looking at all the rows just to get the biggest insert ID  If your table gets big  that could become very inefficient

User · Answer

If you ve just inserted a record into the Customers table and you need the value of the recently populated ID field  you can use the SCOPE IDENTITY function   This is only useful when the INSERT has occurred within the same scope as the call to SCOPE IDENTITY   INSERT INTO Customers ID  FirstName  LastName  Values  23   Bob    Smith    SET  mostRecentId   SCOPE IDENTITY     This may or may not be useful for you  but it s a good technique to be aware of   It will also work with auto-generated columns

User · Answer

select   from tablename order by ID DESC   that will give you row with id 22

User · Answer

Here s how I would make the next ID  INSERT INTO table name               ID              FIRSTNAME              SURNAME               VALUES                          SELECT COALESCE MAX B ID  1 1  AS NEXTID                          FROM table name B                             John2  Smith2    With this you can make sure that even if the table ID is NULL  it will still work perfectly

User · Answer

To find the next  still not used  auto-increment  I am using this function for somewhat years now   public function getNewAI  table                 newAI   false            mysqli   new mysqli DB HOST  DB USER  DB PASSWORD  DB NAME           if mysqli connect errno                  echo  Failed to connect to MySQL      mysqli connect error                        sql    SHOW TABLE STATUS LIKE     table                result    mysqli- gt query  sql            if  result                 row    result- gt fetch assoc                  newAI    row  Auto increment                       mysqli- gt close             return  newAI

User · Answer

You can also use relational algebra  A bit lengthy procedure  but here it is just to understand how MAX   works     E    pID  Table Name  E1    pID  sID    ID     ID  E    E       pID  sID  lt  ID       ID  E    E      Your answer   Table Name   E1    Basically what you do is subtract set of ordered relation a b  in which a lt b from A where a  b   A     For relation algebra symbols see  Relational algebra From Wikipedia

User · Answer

You can do  SELECT MAX ID  FROM Customers

User · Answer

SELECT   FROM Customers ORDER BY ID DESC LIMIT 1   Then get the ID

User · Answer

select max id  from customers

[sql] SQL - How to find the highest number in a column?

Examples related to sql

Examples related to mysql

Examples related to sql-server