Question: Aggregrate function Max() in db

Ads

 
 
 

Share on Google+Share on Google+

Darsana K
Aggregrate function Max() in db
1 Answer(s)      4 years and 4 months ago
Posted in : SQL

Hi everyone i have a question and stuck with this problem. i have a table like Brand in mySql db where brandid(varchar) is generating automatically.. im having a Db connection class and a method for generating id. Mycode for id generation is:

public String GetId(String sql, String strchar) {
    int num;
    String strid, value;
    try {
        stmt = conn.createStatement();
        rst = stmt.executeQuery(sql);
        // System.out.println("stmt executed");
        if (rst.next()) {
            strid = rst.getString(1);
            //   System.out.println("strid is:"+strid);
            if (strid == null) {
                return strchar + "1";
            } else {
                num = Integer.parseInt(strid.substring(2, 3));
                //System.out.println("inside if");
                System.out.println("id is:" + (num + 1));
                num++;
                return strchar + num;
            }

        }
        return strchar + "1" + "hi";


    } catch (SQLException sq) {
        System.out.println("error" + sq);
        return "error";
    }
}

my problem is after generating BR9 max(brandid) will always give BR9 instead of BR10 even if BR10 is inserted.So after a row with BR9 again and again i have Br10 inserted when executing the getid() ,because BR9 is getting after max(brandid).i know one solution that is to give start id as BR1001,but i think it is not obsolete because after the range BR9999 ,max(brandid) will always give BR9999.can anyaone suggest me good solution for that.Thanks in advance.

Ads
View Answers

January 12, 2013 at 12:33 PM


anyway i found answer to my question

use query like

String bid = dbconn.GetId("select max(cast(substring(BrandId,3)as unsigned)) from brand", "BR")

and my db class method shoul be like:

        stmt=conn.createStatement();
        rst=stmt.executeQuery(sql); 
         if(rst.next())
        {   
                num=rst.getInt(1); 
                if(num == 0)
                {
                return strchar + "1";
                }
                else{
                num++;
              //  System.out.println("hi"+num);
                return strchar +num;
                } 

        }

Ads









Related Tutorials/Questions & Answers:
Tutorials   
Java Spring Hibernate Struts Training How to Fetch Data using dropdown option How to get day from date in SQL? Example of sorting results in MySQL Distinct and Limit example in MySQL How to download install and use MySQL workbench? Example of Average, Sum, Min and Max functions in MySQL How to delete data from MySQL? Update SQL Query Example Order by example in MySQL AND and OR Operators in MySQL Query Where clause with query in MySQL How to use select Statement in MySQL? How to insert data into MySQL Table? Create Database and tables in MySQL example of before insert trigger in mysql Failed to Call Procedure MY Sql Query Error - Not able to Understand the issue which query will be run for previous(button) question How to access data yearly from DB in C# ? Fastest way to upload huge raw data in database - c#, Java or stored procedure DB Insert Nb hours between 2 time stamps meets specific criteria mysql select into table from another table example MySQLselect into new table Example code How to create a .mdf file from script (.sql) Please help me out fixing broken relational links in MySQL database after an upgrade How do I upgrade mysql? mysql 4.x to 5.x upgrade problem Upgrading a PHP/MYSQL application? upgrading sql query join phpmyadmin in whm whm php version update php cpanel cpanel mysql downgrade cpanel mysql version upgrade mysql whm upgrade mysql 4 5 cpanel upgrade mysql cpanel server Did my upgrade from mySQL 5.1 to 5.5 work Upgrade to MySQL 5.5 ? - cPanel Mem use upgrade from MySQL 5.1 to 5.5 - cPanel Upgrading MySQL from 5.0 to 5.1 or 5.5 - cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5)cPanel Mysql Upgrade on WHM (Mysql 5.1 to 5.5) - cPanel Updating from MySQL 5.0 to 5.5 cPanel MySQL 5.1 upgrade to 5.5 error Charset problem upon publishing PHP + MySQL website MySQL issue: Table 'data_dictionary.CHARACTER_SETS' doesn't exist SQL Error Version 5.5.15

Ads

 
Advertisement null

Ads