Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 3rd, 2008, 05:47 AM
Authorized User
 
Join Date: Feb 2007
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tricky Queries

Hi All,
This is my first mail in this categrory. And I hope some good response from u all. Actually I am searching some tricky queries(generally ask to write in interviews) along with the answer. Just like--
a) how to delete duplicate rows when there is no primary key field in a single query.

Plz forward, if any of you have some.

Thanks in advance.

 
Old April 3rd, 2008, 07:58 AM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Ummm... you need to write your own questions and then create, test, and understand your own answers. How can you expect an interviewee to answer "tricky" questions that you have no clue as to why the answers are what they are?

--Jeff Moden
 
Old April 12th, 2008, 01:28 AM
Friend of Wrox
 
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok I will help you with your exam question.

a) if you have duplicate rows where every row is a duplicate you need to write a cursor and go through row by row comparing to the previous (assuming you selected in a good order) then delete the current row if it exactly matches every field of the previous row. Best way though is to add a unique value(primary key). Good luck with your homework.

 
Old April 12th, 2008, 12:02 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Cursor? I don't think so....

--Jeff Moden
 
Old April 12th, 2008, 03:55 PM
Friend of Wrox
 
Join Date: May 2006
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default

DELETE f
FROM (SELECT ROW_NUMBER() OVER PARTITION BY Col1 ORDER BY Col2 DESC) AS RecID FROM Table1) AS f
WHERE RecID > 1
 
Old April 12th, 2008, 10:29 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

... besides, the OP wasn't asking for a solution to a problem.... the OP was asking for trick questions that could be asked during an interview.

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
A tricky question mike_remember ASP.NET 1.0 and 1.1 Professional 3 October 27th, 2006 08:17 AM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
Easy but tricky for me... :( Varg_88 Classic ASP Basics 3 January 13th, 2005 11:21 AM
Alright, here's a tricky one... Anubis Access VBA 14 November 19th, 2003 04:22 PM
Tricky SQL fastcorvette Access 2 October 10th, 2003 10:28 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.