Select a distinct record, filtering is not working

Hello EVery I am new to SQl. query to result in the following records.

I have a table with records as

c1  c2            c3                  c4    c5   c6

1  John         2.3.2010 12:09:54     4     7    99        
2  mike         2.3.2010 13:09:59     8     6    88   
3  ahmad         2.3.2010 13:09:59     1     9    19   


4  Jim        23.3.2010 16:35:14      4     5    99   
5  run        23.3.2010 12:09:54      3     8    12 

I want to fecth only records. i.e only 1 latest record per day. If both of them happen at the same time, sort by C1.so in 1&3 it should fetch 3.

3  ahmad         2.3.2010 14:09:59     1     9    19   
4  Jim        23.3.2010 16:35:14      4     5    99

I have got a new problem in this. If i filter the records based on conditions the last record is missing. I tried many ways but still it is failing. Here update_log is my table.

SELECT * FROM update_log t1 
WHERE (t1.c3) = 
(
    SELECT MAX(t2.c3) 
    FROM update_log t2 
    WHERE DATEDIFF(dd,t2.c3, t1.c3) = 0 
)
and t1.c3 > '02.03.2010' and t1.modified_at <= '22.03.2010' 

ORDER BY t1.c3 ASC. But i am not able to retrieve the record

4  Jim        23.3.2010 16:35:14      4     5    99

I dont know this query results in only

  3  ahmad         2.3.2010 14:09:59     1     9    19   

The format of the column c3 is datetime. I am pumping the data into the column as using $date = date("d.m.Y H:i",time()); -- simple date fetch of today.

Another query that i tried for the same purpose.

select * from (select convert(varchar(10), c3,104) as date, max(c3) as max_date, max(c1) as Nr from update_log group by convert(varchar(10), c3,104)) as t2 inner join update_log as t1 on (t2.max_date = t1.c3 and convert(varchar(10), c3,104) = date and t1.[c1]= Nr) WHERE t1.c3 >= '02.03.2010' and t1.c3 <= '16.开发者_C百科04.2010' . I even tried this way..the same error last record is not coming..

Following steps should produce the results you are after

• Find max c3 for every day.
• Join the results with your original table, witholding only the max c1 values.

SQL Statement (Edited)

DECLARE @update_log TABLE (c1 INTEGER, c3 DATETIME)

INSERT INTO @update_log
SELECT 1, '3.2.2010 12:09:54'
UNION ALL SELECT 2, '3.2.2010 13:09:59'
UNION ALL SELECT 3, '3.2.2010 13:09:59'
UNION ALL SELECT 4, '3.23.2010 16:35:14'
UNION ALL SELECT 5, '3.23.2010 12:09:54'

SELECT  c1 = MAX(l.c1), l.c3
FROM    @update_log l
        INNER JOIN (
          SELECT  c3_max = MAX(c3)      
          FROM    @update_log
          WHERE   c3 > '3.2.2010 00:00:00'
                  AND c3 < '3.24.2010 00:00:00'
          GROUP BY
                  CONVERT(VARCHAR(10), c3, 101)
        ) l_maxdate ON l_maxdate.c3_max = l.c3
GROUP BY
        l.c3

Notes

You should read the FAQ regarding as to how this site operates. As has been mentioned, Stack Overflow is not intented to be used like one would use a newsreader where you ask follow-up questions after an answer has been given by creating a new answer.

You should edit your question for any additional information or use the comments. If the additional information is that much that it in effect changes the entire question, you should consider making a new question out of it.

That being said, enjoy SO.

Assuming c1 is unique, hopefully even the primary key. This also assumes the use of SQL Server 2008 for the DATE data type.

SELECT t1.* 
FROM update_log t1
WHERE t1.c3 > '02.03.2010' 
  AND t1.modified_at <= '22.03.2010'
  AND t1.c1 IN 
    ( SELECT TOP 1 c1 
      FROM update_log t2
      WHERE CAST(t1.c3 As DATE) = CAST(t2.c3 As DATE)
      ORDER BY c3 DESC, c1 DESC
    )

OK, Now i understood. actually i wrote the query for the same purpose this way.

select * from #temp 
select * from
(select max(c1) as nr from 
(select convert(varchar(10), c3,104) as date, max(c3) as max_date
 from #temp  where 
 convert(varchar(10),c3,104) >=  '02.02.2010' and 
convert(varchar(10),c3,104) <= '23.02.2010'
group by convert(varchar(10), c3,104))
 as t2 inner join #temp as t1 on (t2.max_date = t1.c3 and 
convert(varchar(10), c3,104) = date)  
group by convert(varchar(10),max_date,104))
as t3 inner join #temp as t4 on (t3.nr = t4.c1 )

If i change these 2 lines to c3 >= '02.02.2010' and c3 <= '24.02.2010'. It is working fine. but the query that i have posted is not able to filter the records properly based on dates.

I want to know where i went to wrong to enhance my knoweldge rather than just copying ur query:-)