problem for group query

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

0	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

0	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

0	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

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

0	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

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

Latest Posts