row-by-row operation without cursor

Hi all,
I have a table with customer data. Ik would like to query this table for duplicate entries, based on certain criteria.
My query to find these records:

SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1

So far so good. What I would like is to show some other data from the duplicate records. Example: suppose the above query
finds 2 records having the same criteria (postcode, huisnr, huisnrtv). How can I select other data from the
same table (e.g. name, date of birth) without using a cursor (is too expensive)? I thought of using a derived query but
I can't figure out how to do it. My desired result looks something like this:

postcode huisnr huisnrtv name date of birth
----- --- ---- ----
1111AA 13 a Smith 12/3/70
1111AA 13 a Clinton 10/2/72
2222BB 22 Bloomberg 1/8/61
2222BB 22 Bloomberg 9/2/79
2222BB 22 Pataki 2/4/71
etc.

Thanks in advance.

Diederikselect *
from tblRelatie r
join
(
SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1
) x on r.postcode=x.postcode and r.huisnr=x.huisnr and r.huisnrtv=x.huisnrtv|||Vezi daca merge asta:

select tblRelatie.* from tblRelatie join
(SELECT postcode, huisnr, huisnrtv
FROM tblRelatie
GROUP BY postcode, huisnr, huisnrtv
HAVING COUNT(*) > 1) T1
on tblRelatie.postcode=T1.postcode and tblRelatie.huisnr=T1.huisnr and tblRelatie.huisnrtv=T1.huisnrtv
order by tblRelatie.postcode, tblRelatie.huisnr, tblRelatie.huisnrtv

ionut calin

Row numbers on export

HI

I have written a script to export data from customer table to another crm package, on the export they require the first column to be numbered 1 - .... say 1000 or how ever many rows there will be.

Is it possible?

Thanks

Rich

Are you talking about a batch count? Or does every row have to have a number?

If it's batch count then use DTS add a global variable, assign the count, then use the file system object to insert the count into the file after the export

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Okay after reading it again I think you want count for each row

In sql 2005 you can use ROW_NUMBER in 2000 you can use IDENTITY with a temp table

2000 version

SELECT IDENTITY(INT, 1,1) AS Rank ,*
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT * FROM YourTable
ORDER BY SomeColumn

SELECT * FROM #Ranks ORDER BY Rank

2005 version

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn) AS 'rownumber',*
FROM YourTable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

is there not a function i could use in the statement? , i am not going to use dts

thanks

|||

do a select count(*) from (your query here)

union all

your original query

example in pubs on SQL server 2000

select convert(varchar(30),count(*)), '','','','','','','',''
from authors
union all
select au_id, au_lname, au_fname, phone, address, city, state, zip, contract
from authors

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Thanks Denis, i will go the temp table route

Row Locking in SQL via ADO (VB 6)

Hi,

I'm trying to use the pessimistic row locking of SQL to get following result.

When a customer form is openend, the row should be locked for writing.
This lock should be left open until the user closes the customer form.

I cannot use transactions because there can be more then 1 customer form open in the same app. In ADO a connection is IN transaction or is NOT, nested transactions are not supported.

How can I keep this row locked on SQL and this until I unlock it or the connection is broken ( in case of problems on client machine )?
And how can I see on another machine of this row ( customer ) is already locked so I can open him in read-only?

For the moment I'm using extra fields that hold the info wether the customer is locked en by whom. But that's on application level, not on DB-level.

I hope this is clear enough.I've often wondered if there is any reason that justifies pessimistic locking. So far I haven't found one. I recommend shutting down the SQL Server to get pessimistic locking... If the box is off, no other user can modify your data, and it makes the scaling problems caused by pessimistic locking less of a problem.

To answer your question more directly, yes pessimistic locking can be done using ADO. It has been a long time since I've had any reason to try to hurt myself that badly after I established that it was possible, so I'm fuzzy on the details.

-PatP|||This may be what you're looking for:

http://www34.brinkster.com/a213855/|||Pat,

I'm convinced that pessimistic locking is not the ideal solution.
How should i take care of the record-locking then?

Regards,

Sven Peeters|||Ummmm, optimistic locking (http://search.microsoft.com/search/results.aspx?qu=%22optimistic+locking%22&View=msdn&st=b&c=0&s=1&swc=0)? I especially reccomend An update on UPDATing (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev00/html/vb00e1.asp), but there are lots of good articles to read!

-PatP