hi, I appreciate the time you spared for my problem.
Thanks, that helped :) Regards Anil -----Original Message----- From: Bruce Feist [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 2:47 PM To: Anil Garg Subject: Re: database query. There really isn't enough information for me to answer without making some assumptions. So, I'll make those assumptions: 1) For all input rows with a given value of Tndr, you want to produce an output row of the form [lowest value] followed by a hyphen followed by [highest value] if more than one such input row exists 2) For all input rows with a *unique* value of Tndr, you want to produce an output row equal to the input row 3) In 1), 'lowest' and 'highest' can be done with the sql max() and min() functions -- this contradicts your example below, because you seem to be doing numeric comparisons relying on the meaning of alphanumeric text. If your example is right, that makes things *much* harder. 4) You'll settle for having only output lines in the form of 1). If so, the following SQL would work with most DBMSs; I'm not sure about MySQL in particular: select varchar(Tndr) + ' > ' + varchar(min(Detail)) + ' - ' + varchar(max(Detail)) from thetable group by Tndr; If you really want both formats 1) and 2), try: select varchar(Tndr) + ' > ' + varchar(min(Detail)) + ' - ' + varchar(max(Detail)) from thetable group by Tndr having count(*) > 1 union select varchar(Tndr) + ' > ' + varchar(min(Detail)) from thetable group by Tndr having count(*) = 1; Bruce Feist Anil Garg wrote: >Hi, > >I have a table as shown with approx 500 lines: > >--------------------------------------------------- >Tndr | Detail | >---------------------------------------------------- >34 | one | >47 | two | >34 | three | >55 | four | >47 | five | > > >There can be multiple 'tndr' fields with same values. > >I need to do something so that i can get the output as following: > >34 > one - three >47 > two - five >55 > four > >Is there a direct query to do that OR any ideas on what lines shall i think >to modify my database(if needed). > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
