Select a distinct record, filtering is not working..
- by help_inmssql
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.04.2010' . I even tried this way..the same error last record is not coming..
