hi master
Sir
I have many duplicate record in my table
I use this query
STDCODE COUNT(*)
-------------------- ----------------------
021-00-11133 1
021-00-11327 1
021-00-11329 2
021-00-11400 3
021-00-11401 1
021-00-11473 2
021-00-11490 1
021-00-11498 2
021-00-11521 1
021-00-11529 2
021-00-11531 2
021-00-11535 1
021-00-11548 1
021-00-11558 2
But sir I need only one record for every student
Such as
STDCODE COUNT(*)
-------------------- ----------------------
021-00-11133 1
021-00-11327 1
021-00-11329 1
021-00-11400 1
021-00-11401 1
021-00-11473 1
021-00-11490 1
021-00-11498 1
021-00-11521 1
021-00-11529 1
021-00-11531 1
021-00-11535 1
021-00-11548 1
021-00-11558 1
And in last total student
How I avoid the duplicate record and show only one record for every etudend from query
Please give me idea
Thank
How I avoid the duplicate record and show only one
It's very easy you have to Create Formula Column and write a query in it
function COUNTFormula return NUMBER is
TOTAL NUMBER(25);
begin
SELECT COUNT(DISTINCT NO)
INTO TOTAL
FROM Tables Means Copy only the tables names from Query and as well as the conditions
WHERE A = A
AND B = B
AND C = C
AND B.NO BETWEEN _TO AND _FROM;
RETURN TOTAL;
end;
100% Work I recently used this function if you do not under stud do not hesetate to reply.
Thanks
Cheers
Najm
function COUNTFormula return NUMBER is
TOTAL NUMBER(25);
begin
SELECT COUNT(DISTINCT NO)
INTO TOTAL
FROM Tables Means Copy only the tables names from Query and as well as the conditions
WHERE A = A
AND B = B
AND C = C
AND B.NO BETWEEN _TO AND _FROM;
RETURN TOTAL;
end;
100% Work I recently used this function if you do not under stud do not hesetate to reply.
Thanks
Cheers
Najm
-
- Posts: 615
- Joined: Mon Sep 18, 2006 1:32 am
- Location: United Arab Emirates
- Contact:
Select A1.*, A1.rowid
from Tab1 A1
where exists (Select 'x' from Tab1 A2
where A1.L_NO = A2.L_NO
and A1.ROWID <> A2.ROWID)
Use This Query To Elimanate The Duplicate Records
-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)
from Tab1 A1
where exists (Select 'x' from Tab1 A2
where A1.L_NO = A2.L_NO
and A1.ROWID <> A2.ROWID)
Use This Query To Elimanate The Duplicate Records
-- To delete all but one of each duplicate row,
-- change the first line to 'Delete'
-- and change the '<>' to '>' (keeps first duplicate)
-- or '<' (keeps last duplicate)
Who is online
Users browsing this forum: No registered users and 6 guests