Re: Intermediate SQL question

Sorry to stick my nose in here, but isn't MIN(Message) a function to return the minimum value
in the Message column? Assuming that Message is a number. Since this isn't the case, this won't
do what he wanted.

However, If your thought was to return the messages in length order based on what dsaf showed
us, then you would want to calculate the length of the string Message and order the results by that
value.

Also, to get this reference to Message to work, you need to add TMESSAGE to the From clause
and add a Where clause linking the two tables.

Hope this helps,

Dave Bechtel

brayasam wrote:

> Just some tricks :)
>
> try this.
>
> SELECT min(Message) as Msgm, TSIMILAR_MESSAGE_TO_MESSAGE.ID,
> Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS CountOfMESSAGE_ID
> FROM TSIMILAR_MESSAGE_TO_MESSAGE
> GROUP BY TSIMILAR_MESSAGE_TO_MESSAGE.ID
> HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));"
>
> Hope this helps.
>
> B. Rayasam.
>
> "dsaf" <zxcv_at_gf.ll> wrote in message
> news:p7jc8.66438$Cg5.3892987_at_news1.calgary.shaw.ca...
> > I prefer leaving SQL to the gurus, but this is a small project, so I have
> > the joy of writing the entire app.
> >
> >
> > My problem
> >
> > I have two tables
> >
> > TMESSAGE
> > with fields
> > message_id, message
> >
> > and
> > TSIMILAR_MESSAGE_TO_MESSAGE
> > with fields
> > id, message_id
> >
> > TMESSAGE.message_id relates to both TSIMILAR_MESSAGE_TO_MESSAGE.message_id
> > and TSIMILAR_MESSAGE_TO_MESSAGE.id as a one to many relationship.
> > TSIMILAR_MESSAGE_TO_MESSAGE.message_id and TSIMILAR_MESSAGE_TO_MESSAGE.id
> > together make the primary key for their table.
> >
> > Now I want to group the similar messages to see which is the largest group
> > of similar messages.
> > This is easy:
> >
> > "SELECT TSIMILAR_MESSAGE_TO_MESSAGE.ID,
> > Count(TSIMILAR_MESSAGE_TO_MESSAGE.MESSAGE_ID) AS CountOfMESSAGE_ID
> > FROM TSIMILAR_MESSAGE_TO_MESSAGE
> > GROUP BY TSIMILAR_MESSAGE_TO_MESSAGE.ID
> > HAVING (((Count(TSIMILAR_MESSAGE_TO_MESSAGE.ID))>1));"
> >
> > This provides a table as follows
> >
> > message_id CountOfMESSAGE_ID
> >
> > 24 7
> > 31 4
> > 36 3
> >
> > The problem is I also want the message returned in the query
> > (TMESSAGE.message).
> > In other words I would like a resultset that looks as follows:
> >
> > message message_id CountOfMESSAGE_ID
> >
> > I am a message 24 7
> > I am a message too 31 4
> > I am a message also 36 3
> >
> > I'm sure it's fairly simple, but I just can't quite figure it out. Any
 help
> > would be most welcome.
> >
> >
> >
> >
  Received on Sat Feb 23 2002 - 15:19:14 CET