If you do this, make sure to SET EQNULL ON before doing the select. In addresses like this, NULL really means "EMPTY", not "UNKNOWN" SET EQNULL ON allows you to compare NULLS as if they were "EMPTY" which is what you want on this comparison.
Dennis McGrath ________________________________ From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Wednesday, October 22, 2008 10:46 AM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: select distinct... If you just want to get the distinct supplier names and addresses, then select distinct is the way to go. SELECT DISTINCT suppliername,address1,address2.....FROM viewname WHERE <your where clause> However, if you need other columns to be selected (other than the ones that are actually distinct), then you need to use group by. Dawn Hast [email protected] wrote on 10/22/2008 11:17:42 AM: > I have a View that I created of Suppliers that contains data from > the main Supplier table, an Inventory table and a Contacts table. > Because the Inventory & Conatcts are 'one to many' to the Main > Supplier table, I end up with multiple Suppliers in my View. I want > to print mailing labels so I want to only print one of each Company. > > I could specify almost every column in my view for my print > statement, but I was thinking there might be a shorter way. > > Lin > > > ----- Original message ----- > From: "Ramsour Mike" <[EMAIL PROTECTED]> > To: "RBASE-L Mailing List" <[email protected]> > Date: Tue, 21 Oct 2008 20:18:29 -0400 > Subject: [RBASE-L] - RE: select distinct... > > Lin: > > That all depends on what you're wanting to accomplish. Obviously > the more columns you specify in your SELECT statement the less > DISTINCTness there's going to be. > > Have you checked out the GROUP BY clause of a SELECT statement? It > can be a powerful tool. > > Here's an example from one of my database: > > SELECT MELTCODE,COUNT(*),SUM(POUNDS) FROM REJECT GROUP BY MELTCODE > > MELTCODE COUNT (*) SUM (POUNDS) > -------- ---------- --------------- > 111-10 196 576,765 > 151-15 4 30,150 > 151-21 2 33,370 > 174-20 1057 681,820 > 180-21 158 200,319 > 181-21 469 1,763,874 > 182-20 228 855,215 > > This command displays a "distinct" record for each MELTCODE in my > REJECT table. In addition to this I aggregated the MELTCODE by > having R:BASE give me a count of all records where a given MELTCODE > exists plus a total of the POUNDS column. > > Again it gets back to what you need to accomplish. > > Hope this helps. > > Mike Ramsour > AK Steel Coshocton Works > Quality Department > Phone/VMS: 740-829-4340 > > From: Lin MacDonald [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 21, 2008 7:13 PM > To: [email protected] > Subject: [RBASE-L] - select distinct... > > I need a push with the 'select distinct' command. My brain just > isn't following along! > > I want to select all rows in my view (dependant on my 'where' > clause), but I only want one instance of each Company to show. Is > there a way to use Select * from... yet still use 'distinct' or do I > need to select (and list) each column ? > > thanks, > > Lin DISCLAIMER: This electronic message together with any attachments is confidential. If you are not the intended recipient, do not copy, disclose or use the contents in any way. Please also advise us by return e-mail that you have received the message and then please destroy. Evergreen Packaging is not responsible for any changes made to this message and / or any attachments after sending by Evergreen Packaging. We use virus scanning software but exclude all liability for viruses or anything similar in this email or any attachment.
