IT Professionals

[bingleguy]

I guess Ys ....

[PARAMASHIVAN]

Has any one used 'Core FTP ' software? Is it any good ?

[PARAMASHIVAN]

Some one pls help. See the query below. when I run it I want all the first name/ last name grouped together, I have included them in the group by aggregate, but the in the out put it does not list first_name, Last_name grouped together

USE hospices
SELECT
dbo._contact.reference,
dbo._contact.first_name,
dbo._contact.last_name,
don2.value_gross AS ValueGross,
don2.value_net AS ValueNet,
'Individual' AS donorType
FROM dbo._Donation don2
INNER Join dbo._Contact ON don2.supporter_id = dbo._Contact.id
WHERE don2.legacy_id is null
and dbo._Contact.dutchess_norfolk_contact=1
and dbo._Contact.deceased=0
and don2.value_gross >=500
group by
dbo._contact.reference,
dbo._contact.first_name,
dbo._contact.last_name,
don2.value_gross,
don2.value_net
ORDER BY ValueGross DESC

[app_engine]

Hey, you're grouping by all the fields that you're selecting.

What value then you want to see aggregated? Is it count(*) or something else?

Your query will only get a hardcoded value "Individual" for each entry of reference, firstname, lastname, valuegross and valuenet.

If you want sum of, say value gross or value net then you need to only group by ref / first name / last name and place the other inside the desired function.

[PARAMASHIVAN]

Thanks na, I orderd by last_name and it works.

:P

many thanks for 'prompt' reply

[PARAMASHIVAN]

use hospices_test
select contact_id,event_id
from _event_response
group by contact_id,event_id
having count(*)> 1


The above statement returned all the duplicates, I have to delete all of them. I tried subquery , it does not work, as it can not return more than one value.

Is there any other way I can do this, Please help, this is quite urgent

[app_engine]

Params,

I've not worked recently on Microsoft SQL-Server and you may possibly want to try their solution given here :
http://support.microsoft.com/kb/139444

For Oracle, I normally use "rowid" as in this sql below :

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;

I'm not sure whether MS-SQL uses rowid, give it a try

[PARAMASHIVAN]

Params,

I've not worked recently on Microsoft SQL-Server and you may possibly want to try their solution given here :
http://support.microsoft.com/kb/139444

For Oracle, I normally use "rowid" as in this sql below :

DELETE FROM our_table
WHERE rowid not in
(SELECT MIN(rowid)
FROM our_table
GROUP BY column1, column2, column3...) ;

I'm not sure whether MS-SQL uses rowid, give it a try

Hello anneh

I have seen the MSDN website, wasn't any help will try your code and let you know.

many thanks for help

[PARAMASHIVAN]

sorry it does not work , there is no rowid in sql server, it is called row_number and I tried that and it doesn't work

no worries thanks any way

[app_engine]

This article also talks about using a temp table (in the case of SQL Server) :

http://database-programming.suite101..._in_sql_server

May be there are other ways, but if you have grants to create a new table, that is possibly a quick (and dirty) solution.