locked
Get Last Record From Given Inputs RRS feed

  • Question

  • User-807418713 posted

    Hello To Everyone

    Field1
    A-11
    A-12
    A-14
    A-15
    A-11/1
    A-11/2
    B-11
    C-11
    A-14/1
    A-15/1
    A-15/5

    This is my table data i want to fetech last record

    For example: if i pass A-11 then it has to show
    Last Record Is : A-11/2

    For example: if i pass A-15 then it has to show
    Last Record Is : A-15/5

    For example: if i pass B-11 then it has to show
    Last Record Is : B-11

    How would be my MS-SQL Query

    Thanking You

    Monday, January 6, 2020 11:04 AM

Answers

  • User77042963 posted
    CREATE TABLE mytable(
       Field1 VARCHAR(6) NOT NULL PRIMARY KEY
    );
    INSERT INTO mytable(Field1) VALUES
     ('A-11')
    ,('A-12')
    ,('A-14')
    ,('A-15')
    ,('A-11/1')
    ,('A-11/2')
    ,('B-11')
    ,('C-11')
    ,('A-14/1')
    ,('A-15/1')
    ,('A-15/5');
    
    
    declare @s varchar(10)='A-11'
    --'B-11'
    ;with mycte as 
    (select 
     *,row_number() Over(Partition by reverse(stuff(reverse(Field1),1, charindex('/',reverse(Field1)) ,'') )
    Order by stuff(Field1,1,isnull(nullif(charindex('/',Field1),0),len(Field1)),'')  desc)  rn
    from mytable)
    
    
    select Field1 from mycte
    Where rn=1 and Field1 like @s+'%'
    
    drop table mytable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 2:48 PM

All replies

  • User452040443 posted

    Hi,

    Try something like this:

    select top(1) * 
    from MyTable
    where Field1 like 'A-11%'
    order by Field1 desc

    Hope this help

    Monday, January 6, 2020 12:13 PM
  • User77042963 posted
    CREATE TABLE mytable(
       Field1 VARCHAR(6) NOT NULL PRIMARY KEY
    );
    INSERT INTO mytable(Field1) VALUES
     ('A-11')
    ,('A-12')
    ,('A-14')
    ,('A-15')
    ,('A-11/1')
    ,('A-11/2')
    ,('B-11')
    ,('C-11')
    ,('A-14/1')
    ,('A-15/1')
    ,('A-15/5');
    
    
    declare @s varchar(10)='A-11'
    --'B-11'
    ;with mycte as 
    (select 
     *,row_number() Over(Partition by reverse(stuff(reverse(Field1),1, charindex('/',reverse(Field1)) ,'') )
    Order by stuff(Field1,1,isnull(nullif(charindex('/',Field1),0),len(Field1)),'')  desc)  rn
    from mytable)
    
    
    select Field1 from mycte
    Where rn=1 and Field1 like @s+'%'
    
    drop table mytable

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, January 6, 2020 2:48 PM
  • User288213138 posted

    Hi Gopi.MCA,

    For example: if i pass A-11 then it has to show
    Last Record Is : A-11/2

    For example: if i pass A-15 then it has to show
    Last Record Is : A-15/5

    For example: if i pass B-11 then it has to show
    Last Record Is : B-11

    As imapsp said, you can use the SQL LIKE Operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

    More information about SQL LIKE Operator you can refer to this link:https://docs.microsoft.com/en-us/sql/t-sql/language-elements/like-transact-sql?view=sql-server-ver15

    Best regards,

    Sam

    Tuesday, January 7, 2020 7:41 AM