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.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