|
Hi ,ALL
If i have this Customer table :
ID | CustomerName | PhoneNum | City |Street
<code>19 jon 555 NK st.5</code>
21 Mark 236 TK KA.st
<code>37 jon 555 NK st.5</code>
45 AJ 800 LA WS.17
We note that Customer Name "Jon" added in this table tow time with deffrint IDs .
In my real database , i have Thousands of this case.
How i can write query return the douplicate record with there IDs ? , like :
ID | CustomerName | PhoneNum | City |Street
19 jon 555 NK st.5
37 jon 555 NK st.5
modified on Monday, December 7, 2009 4:18 AM
|
|
|
|
|
Do some basic research[^]
This is a common problem that has been solved over and over again. The first 2 items look interesting.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear Mycroft ,
i search befor and after post my question. but i did not find the solustion . could you help me please ?!
according my qusetion , my problem is i have tow douplicte record with deffrint ID , when i use Max(ID) or top(ID) functions i will ignor of the IDs ,becuse ID is deffirnt. and i wont to display it as it is with out ignor any IDs . how i can do it ?
|
|
|
|
|
You asked samething a few posts down. You got the reply and as able to do this as well. Now, why are you posting this again?
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Dear d@nish ,
Here i have different case , becuse i have two douplicate record with different ID , and when i use max() or top() function i will be ignor one of the ID ,and this couse problem for me to link to other tables.
so i need to display all the two records with there IDs.
ould you please help me to do that ?!
Thanks
|
|
|
|
|
If you will read Shameel's reply to your earlier post, you should be able to figure it out. Anyways, a general query would be:
select [colNames]
from tableName
group by [colNames]
having count(*) > 1
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
you are right and i know that ! but
if i write this :
select [ID,colNames,Phone,...]from tableNamegroup by [colNames]having count(*) > 1
i will have 0 douplicate record !! , why ? because IDs for each douplicted records are different !!
and if use all columns without IDs ,i will got douplicted record without IDs.
please see the example in my question , i don't need to ignore any IDs.
could you please help me to find way?
Thanks .
|
|
|
|
|
As Luc mentioned, you don't need to group by ID. You will get the duplicate records then.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Dear d@nish ,
see my replay to LUC.
Thanks
|
|
|
|
|
All you need is a variation on SQL code shown by others. Try this:
SELECT ID, otherfields FROM table
GROUP BY otherfields
HAVING COUNT(*) > 1
i.e. don't use MAX(ID) or TOP(ID)
and include all fields ("otherfields") that are relevant for identity checking
|
|
|
|
|
Hi Luc ,you are right,
I Wrote that what you say , but i got error that " you try to excute a query that does not include the specified expression 'ID' as part of an aggregate function"
Thanks
|
|
|
|
|
Use CTE feature of SQL server 2005.....
Example:
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking)
AS
(
SELECT
Product, SaleDate, SalePrice,
Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC)
FROM SalesHistory
)
DELETE FROM SalesCTE
WHERE Ranking > 1
|
|
|
|
|
Dear puri keemti,
i do not use SQL server , i use sql in access 2007.and i dont need to delet the record that douplicated , i need to display it as it is ,with there different IDS .
if it is not clear please see question and my replies.
could you help me ?
thanks for you brother.
|
|
|
|
|
Hi,
This query works for SQL Server2005,but try to convert for Access2007.
A)
CREATE TABLE #tmpData
(
id INT IDENTITY(1,1),
custname VARCHAR(MAX)
)
INSERT INTO #tmpData
SELECT m.Customername
FROM mytable1 m
GROUP BY m.Customername,m.phonenumber,m.city,m.street HAVING COUNT(m.Customername)>1
DECLARE @i AS INT
DECLARE @query AS nvarCHAR(MAX)
SET @i=0
SET @query = 'select * from mytable1 where customername in ('
WHILE(@i<(SELECT COUNT(*) FROM #tmpData))
BEGIN
SET @i=@i+1
SET @query=@query+(SELECT char(39)+custname + char(39) FROM #tmpData WHERE id=@i)+','
END
SET @query=SUBSTRING(@query,1,LEN(@query)-1)
SET @query=@query+')'
EXEC( @query)
DROP TABLE #tmpData
I just played around and found too way to work for Access (I tested it on Access 2003 and not for 2007 version,but i think it should work too for Access2007 version)
B)
select a.id,a.Customername , a.phonenumber, a.city, a.street
from mytable1 a
inner join
(select Customername , phonenumber, city, street
from mytable1
group by customername,phonenumber,city,street
having count(*) > 1) b
on a.Customername = b.Customername
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
modified on Monday, December 7, 2009 9:32 AM
|
|
|
|
|
Try this
Will work for Sql Server(Any version), MySql,Oracle, MSAccess
select * from myTable where customername in (
select customername
from myTable
group by customername,phonenum,city,street
having(count(customername)>1 and count(phonenum)>1 and count(city)>1 and count(street)>1))
Output:
id customername phonenum city street
19 jon 555 NK st.5
37 jon 555 NK st.5
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi,
is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access.
i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data.
please, advice me if this is good approach. or if there is anything else we can do in the datbase design.
(what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
You can make use of check constraint on your table for this. Something like:
Alter YourTableName
check (intCol > 0 and intCol < 100)
Although, you should try and filter out faulty data at the UI only. Why to hit database to know that it is a faulty data? It will make your application slow.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
|
|
|
|
|
Thanks,
oh yeah.. i forgot about the check
and you are right to validate the data in the UI. which i am aleady doing it. the reason why i want to do the validation in the database design level, is to make sure that we are adding the right data and also, to prevent the other developers from playing with the database. our database is shared one and we are using it many applications.
thanks for your reply
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
You need to think about what you are doing, what are you going to do when you get a validation fail, raise an error. Then what are you going to do, trap the error in your DAL/BusinessObject and pass it back to the UI.
This is called PROGRAMMING BY ERROR and is the absolute worst validation model you could build.
Put you validation is either the UI or the BusinessObject. Validation does not belong in the database.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your reply.
i agree with you. but for your information, i am not waiting to get the error from that database in order to catch it in my DAL/BusinessObject.
i am already doing my validation also in the UI. what i really want to make sure is, the data are inserted right. i dont want to keep any chance for developers to play with the application UI to update the database.
now you make me confused. because i am doing the database design. here is another question: if i consider the following in my database design:
- primary keys
- Forien Keys
- Default Values
- Check Constraint
- indexes
- Database Diagrams
is it bad database design?
Hussain Mohammed Saleh Attiya
ISP Technical Manager
Atyaf Telcom - Bahrain
|
|
|
|
|
Yah, I opened my (big mouth) reply before you had replied, got delayed and posted after you.
What you are doing is correct and is the reason constraints are in the database, if you were doing this as the primary validation my argument would apply. The additional elements you raise are all valid and should be used for a well designed database, what you cannot do is expect and error.
With a FK if you decided to try and write the record and trap the error for a FK violation as the primary validation it would be wrong. Having the FK to enforce data integrity is absolutely valid.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Many ways to do validation in database
(1) Constraints
(2) SP
(3) Triggers
|
|
|
|
|
|
Hi,
I have a table where the results are sorted using an "priority" column, eg:
Doc_Value priority
------------------------
aaa 0
xxx 1
bbb 3
ccc 0
aaa 2
I need the SQL results to be in a specific order based on the "priority", but not in ascending or descending order. The order
that I want them in is 1,2,3,0,0,0,0....
Highest priority start with 1 to infinite (9999) number but the lowest priority is 0...
Any suggestion guys?
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Use a case statement in your order by clause
order by case when priority = 0 then 9999 else sequence end
Never underestimate the power of human stupidity
RAH
|
|
|
|