SQL Server - to select last updated records - Asked By muthuraman alexander on 27-Sep-11 07:42 AM

hi all

i have a table like this

S_No   St     DateOfUpdate
--------------------------------------------
131   1   2011-08-03 05:59:06.000
131   0   2011-09-06 06:04:06.000
132   1   2011-09-06 06:04:05.000
133   58   2011-08-18 19:26:24.000
133   2   2011-08-26 15:24:53.000
133   1   2011-09-06 06:04:05.000
134     2   2011-08-26 17:05:19.000
134   1   2011-09-06 06:01:06.000
135   1   2011-08-03 05:59:05.000
135     0   2011-09-06 06:04:06.000

here i want to select the last updated records

like

S_No   St     DateOfUpdate
--------------------------------------------
131   0   2011-09-06 06:04:06.000
132   1   2011-09-06 06:04:05.000
133   1   2011-09-06 06:04:05.000
134   1   2011-09-06 06:01:06.000
135     0   2011-09-06 06:04:06.000


how can i do this
i need help for this


thanks in advance
Muthu
Suchit shah replied to muthuraman alexander on 27-Sep-11 07:46 AM
select distinct s_No , st, Daeofupdate from table
aneesa replied to muthuraman alexander on 27-Sep-11 07:46 AM
select * from table1 order by  DateOfUpdate desc
smr replied to muthuraman alexander on 27-Sep-11 07:50 AM
HI

try this

SELECT TOP 1 S_No,st
FROM Table
ORDER BY  DateOfUpdate DESC

This returns the records with the most recent first and the TOP function gives you only the first record. It works in SQL Server (and should do in Access although I havent tested it), if you are using MySQL or Oracle use the LIMIT function instead of TOP.

aneesa replied to muthuraman alexander on 27-Sep-11 07:51 AM
select S_No   ,St   ,MAX(DateOfUpdate) from tablename group by  S_No   ,St   order by MAX(DateOfUpdate)desc
Riley K replied to muthuraman alexander on 27-Sep-11 07:52 AM


Use this query

 
SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'databasename')
AND OBJECT_ID=OBJECT_ID('TableName')

Regards
Suchit shah replied to muthuraman alexander on 27-Sep-11 08:18 AM

SELECT S_No as ID, St as No, MAX(dateofup) FROM unq

GROUP BY S_No, st

muthuraman alexander replied to Suchit shah on 27-Sep-11 08:24 AM
hi

thanks u for ur reply ....

actually i tried all these ideas
but it was not working for my actual requirement

when i use Max(date ) with order by date desc
or
using TOP1 order by date desc


it returns only last dated records
but my need is to get the latest record from each group ion table

at the same time when i use group by it showing all the records


thanks in advance
Muthu

Robert Kuma replied to muthuraman alexander on 28-Sep-11 04:41 AM
Hi,

There would be many ways to approach this task. For example you could use derived table to find out the most recent update then limit the displayed records to only records matching that derived table:

SELECT main.S_No, main.St, main.DateOfUpdate
FROM _myTable AS main
INNER JOIN (
    SELECT S_No, MAX(DateOfUpdate) AS RecentDateOfUpdate
    FROM _myTable
    GROUP BY S_No
) AS recentUpdates ON main.S_No = recentUpdates.S_No AND main.DateOfUpdate = recentUpdates.RecentDateOfUpdate
ORDER BY main.S_No

Note, I'm using only group by S_No column as in the sample result it seemed that the St column had no meaning for grouping, but only was returned as a result for particular row. The result of derived table "recentUpdates" which returns only recent date for given S_No column, is used then to limit number of rows returned from table. Join could be replaced with WHERE EXIST, if you prefer.

Also instead of GROUP you could enumerate rows in particular order and then display the one you're interested. For example you could use ROW_NUMBER() function as in below example:
    SELECT S_No, St, DateOfUpdate
    FROM (
        SELECT S_No, St, DateOfUpdate, RowPriority = ROW_NUMBER() OVER (PARTITION BY S_No ORDER BY DateOfUpdate DESC)
        FROM _myTable
    ) AS main
    WHERE RowPriority = 1
    ORDER BY S_No

Few words about above example: The PARTITION BY S_No would start enumerating rows from the beginning for every unique S_No value. Ordering rows in descending order would then ensure that most recent date is always enumerated as 1.

I hope these examples will help.
Regards, Robert