SQL query to get last record of every minute

Viewed 1130

I have a table which is updated every second with some values. I want to retrieve the last record of every minute.

I have tried this code, but it is returning all the records.

SELECT 
    t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus  
FROM 
    brands t0 
WHERE  
    t0.cdt BETWEEN '2013-11-15' and '2014-11-15' 
GROUP BY 
    t0.brandID, t0.brandName, t0.cdt, t0.udt, t0.brandstatus,(datepart(minute, t0.cdt) / 1)

My table structure is:

ID  brandname     cdt                          udt  brandstatus addedby
1   khasim  2013-11-01 19:14:18.120 2013-11-15 19:14:18.123 1   1
2   khasim  2013-11-01 19:14:18.121 2013-11-15 19:14:18.123 1   1
3   khasim  2013-11-01 19:14:18.122 2013-11-15 19:14:18.123 1   1          
4   khasim  2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1   1
5   khasim  2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1   2
6   tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1   2
7   abcdef  2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1   4

Expected result is:

 ID  brandname     cdt                          udt  brandstatus addedby

    4   khasim  2013-11-01 19:14:18.123 2013-11-15 19:14:18.123 1   1
    5   khasim  2013-11-02 19:17:57.700 2013-11-15 19:17:57.700 1   2
    6   tanveer 2013-11-03 19:18:05.947 2013-11-15 19:18:05.947 1   2
    7   abcdef  2013-11-04 20:50:06.783 2013-11-15 20:50:06.787 1   4
3 Answers
Related