Post reply

latest record new

  • January 22, 2020 at 12:15 pm

    #3717337

    this is my query

     

    select  PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL

    from CI_PREM_CHAR

    WHERE CHAR_TYPE_CD = 'UNITS'

    AND prem_id = '1589612791';

    i have attached the output : capture

     

    However i want the latest record only i.e

    PREM_ID: 1589612791

    CHAR_TYPE_CD : UNITS

    EFFDT : 13-JAN-18

    ADHOC_CHAR_VAL :40

     

    Please suggest

     

     

     

     

    Attachments:
    You must be logged in to view attached files.

  • January 22, 2020 at 12:18 pm

    #3717339

    Put TOP 1 after SELECT, and ORDER BY EFFDT DESC at the end of your query.

    John

  • January 22, 2020 at 12:26 pm

    #3717340

    hi jhon it is still giving error this is the query

    select  TOP 1 PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL

    from CI_PREM_CHAR

    WHERE CHAR_TYPE_CD = 'UNITS'

    AND prem_id = '1589612791'

    ORDER BY EFFDT DESC ;

    ERROR

    ORA-00923: FROM keyword not found where expected

    00923. 00000 -  "FROM keyword not found where expected"

    *Cause:

    *Action:

    Error at Line: 1 Column: 13

     

  • January 22, 2020 at 12:26 pm

    #3717341

    hi jhon it is still giving error this is the query

    select  TOP 1 PREM_ID, CHAR_TYPE_CD, CHAR_VAL, EFFDT, ADHOC_CHAR_VAL

    from CI_PREM_CHAR

    WHERE CHAR_TYPE_CD = 'UNITS'

    AND prem_id = '1589612791'

    ORDER BY EFFDT DESC ;

    ERROR

    ORA-00923: FROM keyword not found where expected

    00923. 00000 -  "FROM keyword not found where expected"

    *Cause:

    *Action:

    Error at Line: 1 Column: 13

  • January 22, 2020 at 12:32 pm

    #3717344

    This is a SQL Server forum.  Maybe someone here will know why that's not valid syntax in Oracle, but you'd probably be better off posting your question on an Oracle forum.

    John

  • January 22, 2020 at 12:49 pm

    #3717348

    Take a look at the row_number() function, there is plenty out there on the web for "ORACLE TOP N" as a search term in your favourite search engine queries which will show you how to do this

  • January 26, 2020 at 9:04 pm

    #3720470

    select  PREM_ID, CHAR_TYPE https://audacity.onl/_CD, CHAR_VAL  https://origin.onl/ https://mails.tips/temp-mail/

    • This reply was modified 4 years, 11 months ago by  HATYRA.
    • This reply was modified 4 years, 11 months ago by  HATYRA.

Viewing 7 posts - 1 through 6 (of 6 total)

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