You must Sign In to post a response.
  • Category: SQL Server

    How get only update data list in sql server 2008

    hi,
    I want display only latest updated data from my table. in the my table LAST_MODIFIED is my date field. without using group by because there are so many column/field i am using this query.



    Select A.ID, Case When A.MONTH = 1 Then 'JANUARY'
    When A.MONTH = 2 Then 'FEBRUARY'
    When A.MONTH = 3 Then 'MARCH'
    When A.MONTH = 4 Then 'APRIL'
    When A.MONTH = 5 Then 'MAY'
    When A.MONTH = 6 Then 'JUNE'
    When A.MONTH = 7 Then 'JULY'
    When A.MONTH = 8 Then 'AUGUST'
    When A.MONTH = 9 Then 'SEPTEMBER'
    When A.MONTH = 10 Then 'OCTOBER'
    When A.MONTH = 11 Then 'NOVEMBER'
    When A.MONTH = 12 Then 'DECEMBER' End As 'MONTH',
    A.YEAR, A.REGION_ID, Upper(R.REGION) As 'REGION',
    A.CIRCLE_ID, Upper(C.CIRCLE) As 'CIRCLE',
    A.DIVISION_ID,Upper(D.DIVISION) As 'DIVISION',
    A.DISTRICT_ID, Upper(DIV.DISTRICT) As 'DISTRICT',
    A.TALUKA_ID, Upper(T.TALUKA) As 'TALUKA', A.NAME_OF_SCHEME, Upper(S.SCHEME) As 'SCHEME',
    A.WHETHER_SCHEME_IS_NRDWP_OR_OTHER_THAN_NRDWP,
    A.PROGRAMME_OF_SCHEME, A.TOTAL_DESIGN_POPULATION,
    A.ARP_COST, A.MNP_COST, A.PC_COST, A.TOTAL_COST,
    Case When Convert(varchar, A.DATE_OF_AA, 105) = '01-01-1990' Then '' Else Convert(varchar, A.DATE_OF_AA, 105) End As 'DATE_OF_AA',
    Convert(varchar, A.DATE_OF_TS, 105) As 'DATE_OF_TS',
    Convert(varchar, A.DATE_OF_WORK_ORDER_GIVEN, 105) As 'DATE_OF_WORK_ORDER_GIVEN',
    A.WHETHER_REVISION_IS_NECESSARY,
    A.REVISED_COST_OF_SCHEME,
    Convert(varchar, A.DATE_OF_REVISED_AA,105) As DATE_OF_REVISED_AA,
    Convert(varchar, A.DATE_OF_REVISED_TS,105) As DATE_OF_REVISED_TS,
    A.ACCEPTED_TENDER_COST,
    A.GROSS_EXPECTED_UPTO_3_2012,
    A.GROSS_EXPECTED_UPTO_3_2013,
    A.EXPECTED_DURING_THIS_MONTH,
    A.EXPECTED_UPTO_THIS_MONTH_DURING_FINANCIAL_YEAR,
    A.TOTAL_GROSS,
    A.EXPENDITURE_FOR_STARTING_OF_WATER_SUPPLY,
    A.EXPENDITURE_FOR_COMPLETION,
    A.GOI_PROVISION, GOM_PROVISION,
    A.LOAN_NABARD_HUDCO_OTHERS_PROVISION,
    A.TOTAL_PROVISION,
    A.TOTAL_PROVISION_ON_FOR_COVERAGE_SUSTAINABILITY_WATER_QUALITY_2012_2013,
    A.LIKELY_STARTING_MONTH_OF_WATER_SUPPLY,
    A.LIKELY_COMPLETION_MONTH_OF_WATER_SUPPLY,
    A.PHYSICAL_PROGRESS_IN_PERCENTAGE,
    A.STATUS_P_WSCP_WSCH_NEW_SPWH,
    A.TOTAL_VILLAGES_IN_SCHEME,
    A.TOTAL_WADIS_IN_SCHEME,
    A.TOTAL_VILLAGES_WADIS_IN_SCHEME,
    A.STATUS_OF_HABITATION_COVERAGE_NC_PC,
    A.STATUS_OF_HABITATION_SUSTAINABILITY_SB,
    A.STATUS_OF_HABITATION_WATER_QUALITY,
    A.STATUS_OF_HABITATION_NON_NC_PC,
    A.TOTAL_STATUS_OF_HABITATION_AS_PER_CAP99,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_COVERAGE_NC_PC,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_SUSTAINABILITY_SB,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_WATER_QUALITY,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_NON_NC_PC,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_FC,
    A.TOTAL_TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR,
    A.TOTAL_TARGETED_HABITATION_NEXT_MONTH,
    A.CURRENT_STATUS_OF_HABITATION_COVERAGE_NC_PC,
    A.CURRENT_STATUS_OF_HABITATION_SUSTAINABILITY_SB,
    A.CURRENT_STATUS_OF_HABITATION_WATER_QUALITY,
    A.CURRENT_STATUS_OF_HABITATION_NON_NC_PC,
    A.CURRENT_STATUS_OF_HABITATION_FC,
    A.TOTAL_CURRENT_STATUS_OF_HABITATION,
    A.TOTAL_TARGETED_CUMMULATIVE_CURRENT_MONTH,
    A.TOTAL_TARGETED_PREVIOUS_MONTH,
    A.GENERAL_REMARKS,
    A.REASON_FOR_DELAY,
    A.IF_SCHEME_PENDING_MORE_THAN_3_YEARS_SPECIFY_REASON,
    A.STATUS,
    A.SEND_STATUS, A.ALERT_STATUS,
    Max(Convert(varchar, A.LAST_MODIFIED, 105)) As 'LAST_MODIFIED',
    A.MODIFIED_BY,
    A.OTHER_COST, A.OTHER_PROVISION, A.GROSS_EXPECTED_UPTO_3_2012_PER,
    A.GROSS_EXPECTED_UPTO_3_2013_PER,
    A.EXPECTED_DURING_THIS_MONTH_PER,
    A.EXPECTED_UPTO_THIS_MONTH_DURING_FINANCIAL_YEAR_PER,
    A.TOTAL_GROSS_PER, A.PHYSICAL_PROGRESS_UPTO_LAST_YEAR,
    A.SOURCE_TYPE, A.LPCD, A.SOURCE,
    'DATE_OF_COMPLETION' = Case When A.DATE_OF_COMPLETION = '0001-01-01' Then '' Else Convert(varchar, A.DATE_OF_COMPLETION, 105) End,
    A.TACKLED_NON_WATER_QUALITY, A.TACKLED_WATER_QUALITY,
    A.TACKLED_TOTAL, A.COVERED_NON_WATER_QUALITY, A.COVERED_WATER_QUALITY, A.COVERED_TOTAL, A.TARGETED_NON_WATER_QUALITY,
    A.TARGETED_WATER_QUALITY, A.TARGETED_TOTAL, A.NON_TARGETED_NON_WATER_QUALITY, A.NON_TARGETED_WATER_QUALITY,
    A.NON_TARGETED_TOTAL,
    A.LAST_MONTH_NON_WATER_QUALITY, A.LAST_MONTH_WATER_QUALITY, A.LAST_MONTH_TOTAL, A.CURRENT_MONTH_NON_WATER_QUALITY,
    A.CURRENT_MONTH_WATER_QUALITY, A.CURRENT_MONTH_TOTAL, A.CUMMULATIVE_NON_WATER_QUALITY, A.CUMMULATIVE_WATER_QUALITY,
    A.CUMMULATIVE_TOTAL, A.NON_TARGETED_LAST_MONTH_NON_WATER_QUALITY, A.NON_TARGETED_LAST_MONTH_WATER_QUALITY,
    A.NON_TARGETED_LAST_MONTH_TOTAL, A.NON_TARGETED_CURRENT_MONTH_NON_WATER_QUALITY, A.NON_TARGETED_CURRENT_MONTH_WATER_QUALITY,
    A.NON_TARGETED_CURRENT_MONTH_TOTAL, A.NON_TARGETED_CUMMULATIVE_NON_WATER_QUALITY, A.NON_TARGETED_CUMMULATIVE_WATER_QUALITY,
    A.NON_TARGETED_CUMMULATIVE_TOTAL, A.TARGETED_NOT_STARTED, A.TARGETED_STAGE_1, A.TARGETED_STAGE_2,
    A.TARGETED_STAGE_3, A.NON_TARGETED_NOT_STARTED, A.NON_TARGETED_STAGE_1, A.NON_TARGETED_STAGE_2, A.NON_TARGETED_STAGE_3,
    A.TARGETED_NOT_STARTED_NON_WATER_QUALITY,
    A.TARGETED_STAGE_1_NON_WATER_QUALITY,
    A.TARGETED_STAGE_2_NON_WATER_QUALITY,
    A.TARGETED_STAGE_3_NON_WATER_QUALITY,
    A.TARGETED_NOT_STARTED_WATER_QUALITY,
    A.TARGETED_STAGE_1_WATER_QUALITY,
    A.TARGETED_STAGE_2_WATER_QUALITY,
    A.TARGETED_STAGE_3_WATER_QUALITY,
    A.REVISED_ARP,
    A.REVISED_MNP,
    A.REVISED_PC,
    A.REVISED_OTHER,
    A.WHETHER_RE_REVISION_IS_NECESSARY,
    A.RE_REVISED_COST_OF_SCHEME,
    A.RE_REVISED_ARP,
    A.RE_REVISED_MNP,
    A.RE_REVISED_PC,
    A.RE_REVISED_OTHER,
    Convert(varchar, A.DATE_OF_RE_REVISED_AA, 105) As DATE_OF_RE_REVISED_AA,
    Convert(varchar, A.DATE_OF_RE_REVISED_TS, 105) As DATE_OF_RE_REVISED_TS,
    ESTIMATED_BASED_ON_DSR_YEAR
    From NEW_RURAL_MPR_STATEMENT_A A
    Inner Join NEW_RURAL_MPR_REGION_MASTER R On R.ID = A.REGION_ID
    Left Outer Join NEW_RURAL_MPR_CIRCLE_MASTER C On C.ID = A.CIRCLE_ID
    Left Outer Join NEW_RURAL_MPR_DIVISION_MASTER D On D.ID = A.DIVISION_ID
    Left Outer Join NEW_RURAL_MPR_DISTRICT_MASTER DIV On DIV.ID = A.DISTRICT_ID
    Left Outer Join NEW_RURAL_MPR_TALUKA_MASTER T On T.ID = A.TALUKA_ID
    Left Outer Join NEW_RURAL_MPR_SCHEME_MASTER S On S.ID = A.NAME_OF_SCHEME
    Where S.STATUS = 1
    Order By A.LAST_MODIFIED Desc
  • #762629
    Hi,

    It's too difficult to analyse your query, first off all we are not aware of your table structure as well as exact requirement. So, avoid to post full query, give some sample part and your requirement that's enough.

    Coming to your thread you are looking for last modified result.

    use below query

    select Top 1 * from tablename
    order by datefield desc


    As per your post you are expecting last modified record from your table, the above query give you the same.

    Hope this will helpful to you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #762698
    Hai Chandrashekhar B Patil,
    I think you can get the key value based on the last modified date and then pass those keyvalues to get the records as the result. You can use the subquery for this.
    Hope it will be helpful to you.

    Regards,
    Pawan Awasthi(DNS MVM)
    +91 8123489140 (whatsApp), +60 14365 1476(Malaysia)
    pawansoftit@gmail.com

  • #763300
    Hello,

    Please try with this query :-

    SELECT TOP 1 * FROM
    (Select A.ID, Case When A.MONTH = 1 Then 'JANUARY'
    When A.MONTH = 2 Then 'FEBRUARY'
    When A.MONTH = 3 Then 'MARCH'
    When A.MONTH = 4 Then 'APRIL'
    When A.MONTH = 5 Then 'MAY'
    When A.MONTH = 6 Then 'JUNE'
    When A.MONTH = 7 Then 'JULY'
    When A.MONTH = 8 Then 'AUGUST'
    When A.MONTH = 9 Then 'SEPTEMBER'
    When A.MONTH = 10 Then 'OCTOBER'
    When A.MONTH = 11 Then 'NOVEMBER'
    When A.MONTH = 12 Then 'DECEMBER' End As 'MONTH',
    A.YEAR, A.REGION_ID, Upper(R.REGION) As 'REGION',
    A.CIRCLE_ID, Upper(C.CIRCLE) As 'CIRCLE',
    A.DIVISION_ID,Upper(D.DIVISION) As 'DIVISION',
    A.DISTRICT_ID, Upper(DIV.DISTRICT) As 'DISTRICT',
    A.TALUKA_ID, Upper(T.TALUKA) As 'TALUKA', A.NAME_OF_SCHEME, Upper(S.SCHEME) As 'SCHEME',
    A.WHETHER_SCHEME_IS_NRDWP_OR_OTHER_THAN_NRDWP,
    A.PROGRAMME_OF_SCHEME, A.TOTAL_DESIGN_POPULATION,
    A.ARP_COST, A.MNP_COST, A.PC_COST, A.TOTAL_COST,
    Case When Convert(varchar, A.DATE_OF_AA, 105) = '01-01-1990' Then '' Else Convert(varchar, A.DATE_OF_AA, 105) End As 'DATE_OF_AA',
    Convert(varchar, A.DATE_OF_TS, 105) As 'DATE_OF_TS',
    Convert(varchar, A.DATE_OF_WORK_ORDER_GIVEN, 105) As 'DATE_OF_WORK_ORDER_GIVEN',
    A.WHETHER_REVISION_IS_NECESSARY,
    A.REVISED_COST_OF_SCHEME,
    Convert(varchar, A.DATE_OF_REVISED_AA,105) As DATE_OF_REVISED_AA,
    Convert(varchar, A.DATE_OF_REVISED_TS,105) As DATE_OF_REVISED_TS,
    A.ACCEPTED_TENDER_COST,
    A.GROSS_EXPECTED_UPTO_3_2012,
    A.GROSS_EXPECTED_UPTO_3_2013,
    A.EXPECTED_DURING_THIS_MONTH,
    A.EXPECTED_UPTO_THIS_MONTH_DURING_FINANCIAL_YEAR,
    A.TOTAL_GROSS,
    A.EXPENDITURE_FOR_STARTING_OF_WATER_SUPPLY,
    A.EXPENDITURE_FOR_COMPLETION,
    A.GOI_PROVISION, GOM_PROVISION,
    A.LOAN_NABARD_HUDCO_OTHERS_PROVISION,
    A.TOTAL_PROVISION,
    A.TOTAL_PROVISION_ON_FOR_COVERAGE_SUSTAINABILITY_WATER_QUALITY_2012_2013,
    A.LIKELY_STARTING_MONTH_OF_WATER_SUPPLY,
    A.LIKELY_COMPLETION_MONTH_OF_WATER_SUPPLY,
    A.PHYSICAL_PROGRESS_IN_PERCENTAGE,
    A.STATUS_P_WSCP_WSCH_NEW_SPWH,
    A.TOTAL_VILLAGES_IN_SCHEME,
    A.TOTAL_WADIS_IN_SCHEME,
    A.TOTAL_VILLAGES_WADIS_IN_SCHEME,
    A.STATUS_OF_HABITATION_COVERAGE_NC_PC,
    A.STATUS_OF_HABITATION_SUSTAINABILITY_SB,
    A.STATUS_OF_HABITATION_WATER_QUALITY,
    A.STATUS_OF_HABITATION_NON_NC_PC,
    A.TOTAL_STATUS_OF_HABITATION_AS_PER_CAP99,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_COVERAGE_NC_PC,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_SUSTAINABILITY_SB,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_WATER_QUALITY,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_NON_NC_PC,
    A.TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR_FC,
    A.TOTAL_TARGETED_HABITATION_DURING_THIS_FINANCIAL_YEAR,
    A.TOTAL_TARGETED_HABITATION_NEXT_MONTH,
    A.CURRENT_STATUS_OF_HABITATION_COVERAGE_NC_PC,
    A.CURRENT_STATUS_OF_HABITATION_SUSTAINABILITY_SB,
    A.CURRENT_STATUS_OF_HABITATION_WATER_QUALITY,
    A.CURRENT_STATUS_OF_HABITATION_NON_NC_PC,
    A.CURRENT_STATUS_OF_HABITATION_FC,
    A.TOTAL_CURRENT_STATUS_OF_HABITATION,
    A.TOTAL_TARGETED_CUMMULATIVE_CURRENT_MONTH,
    A.TOTAL_TARGETED_PREVIOUS_MONTH,
    A.GENERAL_REMARKS,
    A.REASON_FOR_DELAY,
    A.IF_SCHEME_PENDING_MORE_THAN_3_YEARS_SPECIFY_REASON,
    A.STATUS,
    A.SEND_STATUS, A.ALERT_STATUS,
    Max(Convert(varchar, A.LAST_MODIFIED, 105)) As 'LAST_MODIFIED',
    A.MODIFIED_BY,
    A.OTHER_COST, A.OTHER_PROVISION, A.GROSS_EXPECTED_UPTO_3_2012_PER,
    A.GROSS_EXPECTED_UPTO_3_2013_PER,
    A.EXPECTED_DURING_THIS_MONTH_PER,
    A.EXPECTED_UPTO_THIS_MONTH_DURING_FINANCIAL_YEAR_PER,
    A.TOTAL_GROSS_PER, A.PHYSICAL_PROGRESS_UPTO_LAST_YEAR,
    A.SOURCE_TYPE, A.LPCD, A.SOURCE,
    'DATE_OF_COMPLETION' = Case When A.DATE_OF_COMPLETION = '0001-01-01' Then '' Else Convert(varchar, A.DATE_OF_COMPLETION, 105) End,
    A.TACKLED_NON_WATER_QUALITY, A.TACKLED_WATER_QUALITY,
    A.TACKLED_TOTAL, A.COVERED_NON_WATER_QUALITY, A.COVERED_WATER_QUALITY, A.COVERED_TOTAL, A.TARGETED_NON_WATER_QUALITY,
    A.TARGETED_WATER_QUALITY, A.TARGETED_TOTAL, A.NON_TARGETED_NON_WATER_QUALITY, A.NON_TARGETED_WATER_QUALITY,
    A.NON_TARGETED_TOTAL,
    A.LAST_MONTH_NON_WATER_QUALITY, A.LAST_MONTH_WATER_QUALITY, A.LAST_MONTH_TOTAL, A.CURRENT_MONTH_NON_WATER_QUALITY,
    A.CURRENT_MONTH_WATER_QUALITY, A.CURRENT_MONTH_TOTAL, A.CUMMULATIVE_NON_WATER_QUALITY, A.CUMMULATIVE_WATER_QUALITY,
    A.CUMMULATIVE_TOTAL, A.NON_TARGETED_LAST_MONTH_NON_WATER_QUALITY, A.NON_TARGETED_LAST_MONTH_WATER_QUALITY,
    A.NON_TARGETED_LAST_MONTH_TOTAL, A.NON_TARGETED_CURRENT_MONTH_NON_WATER_QUALITY, A.NON_TARGETED_CURRENT_MONTH_WATER_QUALITY,
    A.NON_TARGETED_CURRENT_MONTH_TOTAL, A.NON_TARGETED_CUMMULATIVE_NON_WATER_QUALITY, A.NON_TARGETED_CUMMULATIVE_WATER_QUALITY,
    A.NON_TARGETED_CUMMULATIVE_TOTAL, A.TARGETED_NOT_STARTED, A.TARGETED_STAGE_1, A.TARGETED_STAGE_2,
    A.TARGETED_STAGE_3, A.NON_TARGETED_NOT_STARTED, A.NON_TARGETED_STAGE_1, A.NON_TARGETED_STAGE_2, A.NON_TARGETED_STAGE_3,
    A.TARGETED_NOT_STARTED_NON_WATER_QUALITY,
    A.TARGETED_STAGE_1_NON_WATER_QUALITY,
    A.TARGETED_STAGE_2_NON_WATER_QUALITY,
    A.TARGETED_STAGE_3_NON_WATER_QUALITY,
    A.TARGETED_NOT_STARTED_WATER_QUALITY,
    A.TARGETED_STAGE_1_WATER_QUALITY,
    A.TARGETED_STAGE_2_WATER_QUALITY,
    A.TARGETED_STAGE_3_WATER_QUALITY,
    A.REVISED_ARP,
    A.REVISED_MNP,
    A.REVISED_PC,
    A.REVISED_OTHER,
    A.WHETHER_RE_REVISION_IS_NECESSARY,
    A.RE_REVISED_COST_OF_SCHEME,
    A.RE_REVISED_ARP,
    A.RE_REVISED_MNP,
    A.RE_REVISED_PC,
    A.RE_REVISED_OTHER,
    Convert(varchar, A.DATE_OF_RE_REVISED_AA, 105) As DATE_OF_RE_REVISED_AA,
    Convert(varchar, A.DATE_OF_RE_REVISED_TS, 105) As DATE_OF_RE_REVISED_TS,
    ESTIMATED_BASED_ON_DSR_YEAR
    From NEW_RURAL_MPR_STATEMENT_A A
    Inner Join NEW_RURAL_MPR_REGION_MASTER R On R.ID = A.REGION_ID
    Left Outer Join NEW_RURAL_MPR_CIRCLE_MASTER C On C.ID = A.CIRCLE_ID
    Left Outer Join NEW_RURAL_MPR_DIVISION_MASTER D On D.ID = A.DIVISION_ID
    Left Outer Join NEW_RURAL_MPR_DISTRICT_MASTER DIV On DIV.ID = A.DISTRICT_ID
    Left Outer Join NEW_RURAL_MPR_TALUKA_MASTER T On T.ID = A.TALUKA_ID
    Left Outer Join NEW_RURAL_MPR_SCHEME_MASTER S On S.ID = A.NAME_OF_SCHEME
    Where S.STATUS = 1) as tbl order by LAST_MODIFIED DESC

    Thanks


Sign In to post your comments