|
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
|
|
|
|
|
Thanks a lot its working. +5 
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Introduce a new table called PriorityOrder with something like:
Priority PriorityOrder
-------------------------
0 5
1 1
2 2
3 3
4 4
And then join your tables with this table on the Priority column and then Order by the PriorityOrder column.
This design gives you the flexibility to change the ordering any time by changing the contents of this table instead of changing code.
|
|
|
|
|
Wow what a nasty solution, he simply wants to move the 0 value records to the end of the sort and you want to create and support another table. So every time a new record is created he potentially needs to update the priority table.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Priorities are usually limited and may be used in more than one table. This is a generic solution which works best if Priority is stored in its own reference table (a.k.a. Master table), in which case the PriorityOrder column can appear in the same table.
|
|
|
|
|
Hi Shameel,
Thanks for your reply to my problem.
Like Mycroft Holmes said, I just want a simply to move the 0 value records to the end of the sort.
Regards.
ma tju
Software Application Engineer
Petaling Jaya,Selangor, Malaysia
Ring Master SB MVP 2008
Petaling Jaya MOP (Otai)
|
|
|
|
|
Hi,
Mycroft's solution is elegant and I like the way he presents the solution.
However, there are a few more ways which will accomplish the task, though again I like Mycroft's solution
declare @t table(docvalue varchar(50),priority int)
insert into @t select 'aaa',0 union all select 'xxx', 1 union all select 'bbb', 3 union all
select 'ccc',0 union all select 'aaa',2
Query1:
select docvalue,priority from @t where priority <> 0 group by priority,docvalue
union all
select * from @t where priority = 0
Query 2:
select distinct * from @t where priority <> 0 group by priority,docvalue
union
select * from @t where priority = 0
Output:
docvalue priority
xxx 1
aaa 2
bbb 3
aaa 0
ccc 0
Niladri Biswas
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
