none
SQL Server Selecting Distinct RRS feed

  • Question

  • Hi I have the below query:

    SELECT *,TRY_PARSE(REPLACE(SUBSTRING(a.ID,                                          CHARINDEX('(', a.ID)+1,                                           100),                                ')',                                '')                        AS int) AS changeCountFROM tx aWHERE a.Customer_Transaction_ID LIKE '%(%)'

    This will give me a list of similar id's and their count. What I mean is as below:

    LV_AAAA_205_123456_E_(2)

    LV_AAAA_205_123456_E_(4)

    LV_AAAA_205_123456_E_(3)

    What I want is just the latest id ie

    LV_AAAA_205_123456_E_(4)

    How can i select distinct based on the highest id. The numbers can go as high as 1000. ie

    LV_AAAA_205_123456_E_(1000)


    Thursday, June 21, 2018 11:21 AM

Answers

  • this you mean?

    SELECT *
    FROM
    (
    SELECT *,
    TRY_PARSE(REPLACE(SUBSTRING(a.ID,CHARINDEX('(', a.ID)+1,100),')','') AS int) AS changeCount,
    ROW_NUMBER() OVER (PARTITION BY LEFT(a.ID,CHARINDEX('(', a.ID)-1) ORDER BY RY_PARSE(REPLACE(SUBSTRING(a.ID,CHARINDEX('(', a.ID)+1,100),')','') AS int) DESC) AS Seq
    FROM tx a
    WHERE a.Customer_Transaction_ID LIKE '%(%)'
    )t
    WHERE Seq = 1
    ORDER BY ID


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Thursday, June 21, 2018 11:38 AM
    Thursday, June 21, 2018 11:27 AM
    Moderator

All replies

  • this you mean?

    SELECT *
    FROM
    (
    SELECT *,
    TRY_PARSE(REPLACE(SUBSTRING(a.ID,CHARINDEX('(', a.ID)+1,100),')','') AS int) AS changeCount,
    ROW_NUMBER() OVER (PARTITION BY LEFT(a.ID,CHARINDEX('(', a.ID)-1) ORDER BY RY_PARSE(REPLACE(SUBSTRING(a.ID,CHARINDEX('(', a.ID)+1,100),')','') AS int) DESC) AS Seq
    FROM tx a
    WHERE a.Customer_Transaction_ID LIKE '%(%)'
    )t
    WHERE Seq = 1
    ORDER BY ID


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by taz 91 Thursday, June 21, 2018 11:38 AM
    Thursday, June 21, 2018 11:27 AM
    Moderator
  • This is perfect thank you so much!
    Thursday, June 21, 2018 11:38 AM
  • This is perfect thank you so much!
    welcome

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Thursday, June 21, 2018 11:43 AM
    Moderator