How to Select MAX Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Select MAX Value

Author

Topic

vineet.tanwar
Starting Member

5 Posts

Posted - 2012-04-11 : 23:45:21

I Have a Table With One Column Name CPOID type varchar(35)
I Used it to save my ID for Purchase Order The Format for this number is : ddMMyy**** where **** is number starting from 1000 and keep on increasing with each new record. now the problem is how to get the max value from this column because if i use max function than the problem is the value 2303121000 is bigger than 0404121001 so i need to compare date first and then get the max increment value.

Table Structure is
CPOID(Varchar 35) Customer Name(nvarchar 100)
(Format ddMMyy****)
1103121000
1503121001
0304121002

I am stuck in this.

Thank you

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-12 : 00:03:13

DECLARE @a AS TABLE(CPOID VARCHAR(35))
INSERT INTO @a
SELECT '1103121000'
UNION
SELECT '1103121001'
UNION
SELECT '1103121002'
UNION
SELECT '1103121003'
UNION
SELECT '1103121004'
UNION
SELECT '1503121001'
UNION
SELECT '1503121002'
UNION
SELECT '1503121003'
UNION
SELECT '0304121002'
UNION
SELECT '0304121003'
UNION
SELECT '0304121004'
UNION
SELECT '0304121005'
UNION
SELECT '0304121005'
UNION
SELECT '2303121000'
UNION
SELECT '2303121002'
UNION
SELECT '0404121001'
UNION
SELECT '0404121002'
UNION
SELECT '0404121003'
UNION
SELECT '0404121004'

SELECT * FROM @a ORDER BY CONVERT(BIGINT, CPOID)
SELECT * FROM @a WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'
SELECT MAX(CONVERT(BIGINT, CPOID)) FROM @a WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

vineet.tanwar
Starting Member

5 Posts

Posted - 2012-04-12 : 00:20:31

DECLARE @a AS TABLE(CPOID VARCHAR(35))
INSERT INTO @a
SELECT '1103121000'
UNION
SELECT '1103121001'
UNION
SELECT '1103121002'
UNION
SELECT '1103121003'
UNION
SELECT '1103121004'
UNION
SELECT '1503121001'
UNION
SELECT '1503121002'
UNION
SELECT '1503121003'
UNION
SELECT '0304121002'
UNION
SELECT '0304121003'
UNION
SELECT '0304121004'
UNION
SELECT '0304121005'
UNION
SELECT '0304121005'
UNION
SELECT '2303121000'
UNION
SELECT '2303121002'
UNION
SELECT '0404121001'
UNION
SELECT '0404121002'
UNION
SELECT '0404121003'
UNION
SELECT '0404121004'

SELECT * FROM @a ORDER BY CONVERT(BIGINT, CPOID)
SELECT * FROM @a WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'
SELECT MAX(CONVERT(BIGINT, CPOID)) FROM @a WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'

Thank you very much for the help but the problem the code you wirte works perfect but when i run it on my table it gives me null.

SELECT MAX(CONVERT(BIGINT, CPOID)) FROM CustomerPO WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'

CPOID CPOIDDate CustomerID CustomerPoNo CustomerPoDate Remarks CompletionStatus Status
0304121000 2012-04-11 1000 NULL NULL Test 0 1
1204121001 2012-04-12 1000 NULL NULL 0 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 00:25:02

why do need to store value like this? why not store them as separate? by storing like this, you're making manipulations difficult.

Read about normalisation.

If you want to live with it. for getting max you have to do something like

number/10000 to get datepart and number%10000 to get number part and then take max of each

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-04-12 : 01:25:16

In the LIKE Statement you need to give DATE as parameter
WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE() - 8, 4) , '.', '') + '%'

For Ex:
If you like max of the todays date value then use GETDATE() in the statement
WHERE CPOID LIKE REPLACE(CONVERT(VARCHAR, GETDATE(), 4) , '.', '') + '%'

Do you want without using parameter, we need to get MAX value from the CPOID column???

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources