max not giving the latest one

  • I have a 2 coloums that has to records

    createdon Kms_QuoteNumber

    2015-07-22 20:26:41.000 15-35553/Rev9

    2015-07-22 21:32:31.000 15-35553/Rev10

    where I run this it only shows me the 1st one. the /Rev10 is the one I

    SELECT max(kms_QuoteNumber )

    FROM quote

    WHERE

    [kms_quotenumber] LIKE '15-35553%'

  • lcarrethers (8/10/2015)


    I have a 2 coloums that has to records

    createdon Kms_QuoteNumber

    2015-07-22 20:26:41.000 15-35553/Rev9

    2015-07-22 21:32:31.000 15-35553/Rev10

    where I run this it only shows me the 1st one. the /Rev10 is the one I

    SELECT max(kms_QuoteNumber )

    FROM quote

    WHERE

    [kms_quotenumber] LIKE '15-35553%'

    You're getting the correct results for your query.

    Compare the same number of characters in each value. 15-35553/Rev9 is greater than 15-35553/Rev1



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • can you post the CREATE TABLE and INSERT scripts so we can reproduce the problem?

  • What about using a different approach?

    This might be what you're looking for.

    SELECT TOP (1) kms_QuoteNumber

    FROM quote

    WHERE [kms_quotenumber] LIKE '15-35553%'

    ORDER BY createdon DESC

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • pietlinden (8/10/2015)


    can you post the CREATE TABLE and INSERT scripts so we can reproduce the problem?

    You don't need those!



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • That works with only '15-35553%

    that was just an example of one type of quote number. I want the make of any quote number added.

    so when I took the where clause out it only gave me one.

    15-35657

  • Alvin Ramard (8/10/2015)


    lcarrethers (8/10/2015)


    I have a 2 coloums that has to records

    createdon Kms_QuoteNumber

    2015-07-22 20:26:41.000 15-35553/Rev9

    2015-07-22 21:32:31.000 15-35553/Rev10

    where I run this it only shows me the 1st one. the /Rev10 is the one I

    SELECT max(kms_QuoteNumber )

    FROM quote

    WHERE

    [kms_quotenumber] LIKE '15-35553%'

    You're getting the correct results for your query.

    Compare the same number of characters in each value. 15-35553/Rev9 is greater than 15-35553/Rev1

    You need to isolate the number(s) that come after /REV and compare those as number, so 9 gets compared against 10. The way you're doing it, the character 9 gets compared against the character 1. 9 is greater than 1.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Considering the following options. Which one is right?

    CREATE TABLE #SampleData(

    createdon datetime,

    Kms_QuoteNumber varchar( 100))

    INSERT INTO #SampleData

    VALUES('2015-07-22 20:26:41.000', '15-35553/Rev9'),

    ('2015-07-22 21:32:31.000', '15-35553/Rev10'),

    ('2015-07-22 20:26:41.000', '15-37779/Rev8'),

    ('2015-07-22 21:32:31.000', '15-37779/Rev7');

    WITH cteRowNums AS(

    SELECT *,

    ROW_NUMBER() OVER( PARTITION BY LEFT( Kms_QuoteNumber, CHARINDEX( '/', Kms_QuoteNumber)) ORDER BY createdon DESC) rownum

    FROM #SampleData

    )

    SELECT *

    FROM cteRowNums

    WHERE rownum = 1;

    WITH cteRowNums AS(

    SELECT *,

    ROW_NUMBER() OVER( PARTITION BY LEFT( Kms_QuoteNumber, CHARINDEX( '/', Kms_QuoteNumber))

    ORDER BY CAST( SUBSTRING( Kms_QuoteNumber, CHARINDEX( '/Rev', Kms_QuoteNumber) + 4, 10) AS int) DESC) rownum

    FROM #SampleData

    )

    SELECT *

    FROM cteRowNums

    WHERE rownum = 1;

    DROP TABLE #SampleData;

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • CREATE TABLE test

    (

    createdon datetime,

    kms_quoteorder varchar(15)

    )

    INSERT INTO test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')

    INSERT INTO test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')

    INSERT INTO test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')

    INSERT INTO test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')

    select * from test

    drop table test

    So all that I want to see is the max one for each kms_quoteorder

  • lcarrethers (8/10/2015)


    So all that I want to see is the max one for each kms_quoteorder

    How do you define the max one?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • lcarrethers (8/10/2015)


    CREATE TABLE test

    (

    createdon datetime,

    kms_quoteorder varchar(15)

    )

    INSERT INTO test VALUES ('2015-07-22 16:13:39.000','15-37779/Rev7')

    INSERT INTO test VALUES ('2015-07-22 19:54:56.000','15-37779/Rev8')

    INSERT INTO test VALUES ('2015-07-22 20:26:41.000','15-35553/Rev9')

    INSERT INTO test VALUES ('2015-07-22 21:32:31.000','15-35553/Rev10')

    select * from test

    So all that I want to see is the max one for each kms_quoteorder

    Base on the above:

    with base as (

    select

    *,

    rn = row_number() over (partition by left(kms_quoteorder,charindex('/',kms_quoteorder) - 1) order by cast(right(kms_quoteorder,len(kms_quoteorder) - charindex('/',kms_quoteorder) - 3) as int) desc)

    from

    test

    )

    select createdon, kms_quoteorder

    from base

    where rn = 1;

    go

  • I get this error

    Msg 537, Level 16, State 3, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

  • That suggests you have kms_quoteorder values that do not contain a '/'. In that case, charindex would return 0, and after subtracting the 1 would give -1, which is indeed invalid.

    It's difficult to suggest a full solution, since apparently we don't have fully representative sample data. If you could provide sample data that shows all the patterns kms_quoteorder uses, that would help a lot.

    Cheers!

  • lcarrethers (8/10/2015)


    I get this error

    Msg 537, Level 16, State 3, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    As I stated, it worked for what you provided. Need more info if the sample data you provided is not 100% representative of your existing data.

  • Sorry about that. The values are for kms_quotenumber are

    EX.

    NULL

    12-00013

    13-01646/Rev1

    13-01646/Rev10

Viewing 15 posts - 1 through 15 (of 39 total)

You must be logged in to reply to this topic. Login to reply