DISTINCT First Name and Last Name for Mail Merge

I'm trying to create a mail merge on my MS SQL database. Many of my owners are entered more than once as they might own multiple properties. I'm trying to write a query where each is only pulled one time and I'm having some trouble. I think I need to use SELECT DISTINCT, but I need to make sure I don't filter out people with the same first name and different last names and vice versa. Thanks!One of my old bosses insisted this could be done using soundex

don't know, but maybe that is an area to start?

http://www.4guysfromrolla.com/webtech/tips/t103101-1.shtml

good luck|||It doesn't need to be that deep.

I thought of another idea. How do I use SELECT DISTINCT to return all rows in my table and only unique address fields?|||I think that a group statement might accomplish this??

as in

select f_name from table group by f_name

I re-read your post...

address validation is a trick...

the address field can be set up many ways, and the street
type designation can also be setup in a whole slew
of different ways...

what does your address field look like (sample data)

thanks
tony|||Soundex is a practically useless algorithm.

You can select distinct First/Last names in a subquery, but then which address would you use if the two records do not agree?

Here is one of the simplest methods:select YourTable.*
from YourTable
inner join --DistinctNames
(select Max(PrimaryKey) as PrimaryKey
from YourTable
group by FirstName,
LastName) DistinctNames
on YourTable.PrimaryKey = DistinctNames.PrimaryKey|||You da' Blindman! Thanks, that's exactly what I needed.|||I hate to post an extension to the question in the same place, but here goes. My owners are listed more than once if they own more than once parcel of land. What I need to do next is continue the merge to each only once, but add up their land acreage amounts and total for each person. Thoughts anyone? Thanks in advance.sql

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Displaying Number of Table Fields Based on Database Values

Hi guys,

I have this problem here. I have this database with fields LB1, LB2, LB3, LB4 and LB5. Each of these fields have their own respective values. I have another database field called ANSCODE to control the number of fields being displayed.

If the value of ANSCODE is 4, I will have to display fields LB1 to LB4. If the value of ANSCODE is 5, i will have to display fields LB1 to LB5. Can this be achieved with a table? Can someone enlighten me on this? Thanks.

Cheers,

Do you have one value for ANSCODE for the entire report, or different values for each detail row? If it's the former, you can define a table with five columns, then based on the value of ANSCODE, hide the column which shows LB5, i.e. set the hidden property on the table column to "=IIF(Fields!ANSCODE.Value=4, true, false)".

If it's the latter, you might have different number of fields shown in different table rows? In that case, you can hide the textbox for LB5 using the same expression above.

|||

look at this link perhaps it' will help you

http://www.codeproject.com/dotnet/DynamicReport.asp?df=100&forumid=205933&exp=0&select=1195355

Display the total sum of rows found

I'm sure there is an easy answer to this question, it's just I couldn't find it in any of the books that I own.

If I do a SELECT statement that finds say 1000 rows that have been matched, is it possible to return the sum of rows found, i.e. 1000 and at the same time LIMIT the rows returned to 20.

Basiclly, I want to show the user 20 rows at a time but also the total number of rows found, i.e 1000. I hope I have explained things clear enough.

Many thanks in advance.I don't think it's possible in one statement. You have to run 2 statements:

select count(*)from table;

select * from table where rownum <= 20;

or you can have one additional column in your result filled with the count

select
(select count(*) from table),
t.*
from table t
where rownum <= 20;

or have one additional row, but you have to know how many columns you have in your result. e.g. for 4 columns

selce count(*), count(*), count(*), count(*) from table
union all
select * from table
where rownum <= 20;|||Thank you very much for your prompt reply.

With a little bit of tweaking, I managed to get the following statement to work exactly how I like. Basically, out of a table of 3000 rows, it returns 1300 rows, of which I want to display 20 rows only plus at the end of the table one extra row of the count (in this case 1300).

SELECT product, price from electronics
WHERE sid = '187'
LIMIT 20
UNION ALL
SELECT count(*), count(*) from electronics
WHERE sid = '187'

However, as I want to eventually do a sort on the 1300 rows returned, I think I would like to have an extra column of the count instead of an extra row of the count, i.e. 'product', 'price', 'count'. I know it's inefficient to have an extra colomn all with the same sum but I don't really want to do a sort and then mix the count in with it. A solution to adding an extra column with the count would be much appreciated.

Or, is it possible to have the above working select statement but with the count as the first row, and then somehow extract the first row using perl before I do a sort on the 1300 rows returned.

Thanking you again.|||I'm not sure, but I think, it could work when you first select count(*) then UNION ALL your select ordered by whatever. Do not order final result which includes count.

SELECT count(*), count(*) from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY|||now I had a chance to test my last suggestion (previous post). You'll have to use something like:

SELECT count(*) product, count(*) price from electronics
WHERE sid = '187'
UNION ALL
SELECT product, price from
(SELECT product, price from electronics
WHERE sid = '187'
ORDER BY price DESC
LIMIT 20 -- I don't know if this goes before ORDER BY
) X;

otherwise most likely your count won't be in first line. the problem is some databases don't allow ORDER BY in views, subqueries, derived tables...|||Again thank you for your replies. Unfortunatly, I can't seem to get your examples to work. I've managed to come up with the statement below which does exactly what I want i.e. with the count as the first row, this is only possible because I've set the 2nd column of the count row to 0.00, and hopefully as there will never be a product whose price is 0.00 then when I do a ASC sort it should always become the first row.

Additionally, as the count is a number and is in the product column (products are normally described in words), a ASC sort should always put that as the first row.

Of course everything falls down if a product is described as a number or it's price is 0.00, rare but could happen.

SELECT product, price from electronics
WHERE sid = '187'
UNION ALL
SELECT count(*), '0.00' from electronics
WHERE sid = '187'
ORDER BY price ASC
LIMIT 20

I'm sure there is a proper solution, just haven't found it yet. What I really want is to have an extra column with the count in it, i.e.

Table has 3000 rows, rows returned from search is 1300

Product Price Count
--------------
JVC AV27D202 125.00 1300
Panasonic CT32SF37 150.00 1300
Pioneer PDP505HD 200.00 1300
Sharp 27NS100 250.00 1300
etc

instead of

Product Price
----------
1300 0.00
JVC AV27D202 125.00
Panasonic CT32SF37 150.00
Pioneer PDP505HD 200.00
Sharp 27NS100 250.00
etc

By the way, my database is MySQL. Many thanks.|||You could use negative price (like -1.00), and NULL description for your count, it should be on the top allways (row solutions).

did you also try something like:

SELECT
product,
price,
(SELECT count(*) from electronics WHERE sid = '187') cnt
from electronics
WHERE sid = '187'
LIMIT 20

this could be your "column" solution.
what DB do you use?|||My database is MySQL. Great idea about the negative price and NULL. I shall use the row solution if nothing else works.

The column solution however doesn't seem to work, although I can see what you are doing. It falls down I think because it doesn't like the syntax "(SELECT count(*) from electronics WHERE sid = '187') cnt" as a means of creating a third column. Both select statements work perfectly on their own but not together.

Many thanks.|||yes, I think problem is: MySQL doesn't support subqueries. Maybe it's not true in newer versions, but I'm sure this feature wasn't supported by some older versions