🚀 go-pugleaf

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@gf.ll> wrote in message
> news:p7jc8.66438$Cg5.3892987@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.
> >
> >
> >
> >