SQL queries to fetch some records

Dear friends ,

Can you please share with me SQL quesries for

1) Deleting duplicate rows from a particular table.

2) Fetch say 29th row from a table having 40 rows

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by vamshi on March 26, 2010 at 10:41am

can I have the table structure please. So that I can think of a solution.

▶ Reply

Permalink Reply by Ana B on March 26, 2010 at 11:37am

Could anyone provide the basic sql queries that a Tester should know/has to use very often.

Thank you!

▶ Reply

Permalink Reply by Priya Singh on March 26, 2010 at 11:56am

For the first one Deleting the dupliate records
table name = Student
-------------------------------------------
Rno Name Address
1 Ravi Bangalore
2 Shyam Delhi
3 Amit Meerut
4 Ravi Bangalore
5 Amit Meerut

I dont think u need a table for second problem.

▶ Reply

Permalink Reply by vamshi on March 26, 2010 at 12:02pm

I think first one will be done using Mohan's link.

Need to think for the second one. Will try my best and let u know.. is that u need exact 29th record??if its so,then u can retrieve it by placing the condition as ' Where rno=29'.

▶ Reply

Permalink Reply by Kalyani on March 26, 2010 at 1:54pm

Hi Priya,

For the Second Question
Select * from where rownum=29
(This query is for oracle)

▶ Reply

Permalink Reply by Mukesh Ranjan on March 26, 2010 at 2:31pm

delete from table_name
where rowID not In (Select min(rowID) from tablename group by col1, col2...)

▶ Reply

Permalink Reply by Priya Singh on March 26, 2010 at 4:57pm

yaa mukesh , I think this is the correct one..thanks..!!
Bt can u please tell how wud it work........?

▶ Reply

Permalink Reply by vamshi on March 26, 2010 at 5:53pm

I tried the Logic of mukesh in 'SQL Yog' but had some syntax probs....I am also waiting for his explanation.

▶ Reply

Permalink Reply by Mukesh Ranjan on March 26, 2010 at 5:56pm

Hi Priya,
Hope you will get some idea from this.

Delete all rowids that is BIGGER than the SMALLEST rowid value (for a given key).

▶ Reply

Permalink Reply by Mukesh Ranjan on March 26, 2010 at 5:58pm

This query removes rows based on the column names specified in the GROUP BY clause. If you specify only one column name it will remove all duplicate records for that column. If you want to delete exact replica's of the same row - use all the column names in the GROUP BY.

Regards,
Mukesh

▶ Reply

Permalink Reply by vamshi on March 29, 2010 at 3:53pm

Hai Mukesh, I tried with ur logic but I am failing to execute it...plz give me a table as an example and the command u used to execute it...so that it will be helpful to me.

▶ Reply

Permalink Reply by Mukesh Ranjan on March 29, 2010 at 4:47pm

Hi,

In the subquery I Select a unique rowid from all the rowid's It can be MAX, MIN any one and the group by clause should include all the UNIQUE columns I desire. Say I want a composite primary key then the group by should be group by id1,id2 ...

The the subquery returns one record for the dupliacte ID's and I delete all of them that are not in the subquery and that deletes all the dupliacte rows from the database.

Hope this will help you...