Hi,
I have 322 records of which 20 records have one column same which make them duplicate records for me so i want to get the total 302 records which means the records .

i cant use like select count(*),col_name from table_name
GROUP BY col_name
having count(*) >1; as it would eleminate the occurance of the record.

ANy suggestion please.

Thanks
-R

Is this an answer?
select DISTINCT col_name from table_name;

No that doesn't solve my purpose as the entire record is not duplicate only some columns are and so it would be a distinct record anyways.

I'm not well at English, so I couldn't understand your requirements fully.
Could you provide some sample data and required results?

I have records this way..
cola colb colc
5710 123 abc00
5710 123 def00
5720 345 abc00
5720 345 def00


and i want to get

cola colb colc
5710 123 abc00
5720 345 abc00

and my preference is based on cola and i cant make any change to the data i just have execute privs.

Hope this makes it clear

Thanks
-R

No it does not. Why do you choose "5710 123 abc00" and not "5710 123 def00"?

when i have a single record based on cola it wud have abc00 or def00 for colc so its ok but when i have 2 records one with abc00 and one with def00 for the same value of cola it is supposed to be the one with abc00( and i just have these 2 values for colc )

Can i use like rowid and rowid+1 and chk the cola and retrieve the first value of cola.
I'm confused how to go ahead on this


Thanks
-R

Since you did not post the actual requirements, you will have to adapt the above to your data yourself.

PS. Please refrain from using lolspeak on this board - some people find it not just annoying but disrespectful.

Nick is right (not only on the lolspeak). You need to do some sort of aggregation. The values in your column in question define the groups and in each group you have to figure out which record to choose for the result set.

I joined this forum just today..i dont know what lol speak is but i think its about the dummy data i provided..sorry if thats against the rules..thanks for your help

-R

"lolspeak" is the "language" commonly used in text messaging, where you substitute abbreviations in place of the regular words, don't use punctuation marks or proper capitalization, e.g.

It may be faster to type, which I doubt, but it's quite difficult to read.

To make your requirements clearer(for me), you can add more rows for your example.

For these rows of data.
cola colb colc
5710 123 abc00
5710 123 def00
5710 135 abc00
5710 135 aaa00
5720 345 abc00
5720 345 def00
5720 111 ghi00

What do you want to get?

cola colb colc
5710 123 abc00 -- this?
5710 135 aaa00 -- or this?, or both?
5720 ??? ?????

Try something like this:
(COLA is the col with duplicated values)
SELECT TBLX.COLA, TBLX.COLB, TBLX.COLC, ...
FROM ( SELECT TBL1.*, ROW_NUMBER() OVER () RID
FROM TBL1 ) TBLX
WHERE TBLX.RID = (SELECT MIN(B.RID) FROM TBLX A
WHERE A.COLA = TBLX.COLA)

Saludos, arlf.

Or a bit simpler, in case you want to see just one row per value of cola:
(See also Nick's post from Tuesday for a yet simpler solution, in case you want to see one row per (cola, colb) combination.)