problem for group query

Posted by Abhi_patil under Sql Server on 1/18/2011 | Points: 10 | Views : 1002 | Status : [Member] | Replies : 7
Hello friends,

I have table containing data as follows

Id Date Comment fromId toId
1 1/11/2011 7:06:16 PM Test1 2 8
2 1/11/2011 7:06:16 PM Test1 2 8
3 1/11/2011 7:06:16 PM Test1 2 8
4 1/11/2011 7:06:16 PM Test1 2 8
5 1/11/2011 7:06:16 PM Test1 2 8
6 01/13/2011 9:10:16 PM Test2 5 8
7 01/13/2011 9:10:16 PM Test2 5 8
8 01/13/2011 9:10:16 PM Test2 5 8
9 01/13/2011 9:10:16 PM Test2 5 8
10 01/14/2011 11:10:16 PM Test3 6 9


Now i want to show the record as following for toId=8
Date comment fromId toId
01/12/2011 7:06:16 PM Test1 2 8
01/13/2011 9:10:16 PM Test2 5 8

What should be the select query for this please help




Responses

Posted by: T.saravanan on: 1/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Abhi_patil,

Refer the following query.....

Select Date, comment, fromId, toId from table where toId=8 group by Date, comment, fromId, toId 


Here table is your table name. Try this...

Cheers :)

Thanks,
T.Saravanan

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 1/18/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Hi,

I dont see a record 01/12/2011 7:06:16 PM Test1 2 8 in your data listed.

Thanks
Karthik
www.f5Debug.net

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Abhi_patil on: 1/18/2011 [Member] Starter | Points: 25

Up
0
Down
Hi T.saravanan

using this query i will get 9 rows. but i need just only two rows.

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 1/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Abhi_patil,

Sorry for my mistake.Now i update my answer...

Select Date, comment, fromId, toId from table where toId=8 group by Date, comment, fromId, toId 


Cheers :)

Thanks,
T.Saravanan

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Abhi_patil on: 1/18/2011 [Member] Starter | Points: 25

Up
0
Down
Hi T.saravanan
as per u said i connot get as i want, if i use date in group by i am getting 9 rows because date differs in microseconds

also in my table there are other more columns also, i just have to read all columns but only we should group by using fromId

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 1/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Abhi_patil,

Ofcourse based on group condition output data comes.In your table date column have different date,its differ with milliseconds so its also comes in your output.Even if any column value change means that record also comes.

If you get the data only based on date not for time means use Stored procedure you can achieve your output otherwise tell me
what's your exact output do you want?

Thanks,
T.Saravanan

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 1/18/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Abhi_patil,

Try the below query...But date column does not come with time...

Select distinct toId, Convert(datetime,Convert(varchar(12),Date,106)), comment, fromId from table where toId=8 


Here I am used two convertion in date column for get distinct value.
First convert into varchar then convert into datetime...

Try this...

Thanks,
T.Saravanan

Abhi_patil, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response