Interview Question

Question

Post reply

Interview Question

gary1

SSC-Insane

Points: 21152
More actions
August 31, 2015 at 3:23 pm

#300152

There is a table call Table1 and it has 2 columns SSN - unique column and timestamp
There are duplicates values in SSN. How to find the earliest of those duplicates? what is the t-sql query?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

Trick question isn't it?

UNIQUE constraint: used to "...make sure that no duplicate values are entered..." So you can't have duplicates.

-SQLBill

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQLBill (8/31/2015)
Trick question isn't it?
UNIQUE constraint: used to "...make sure that no duplicate values are entered..." So you can't have duplicates.
-SQLBill

no, it's a classic question on the 70-461 test. the prep video from Microsoft on you tube gives a decent example of this, if you care to watch it.

Exam Prep: 70-461 - MCSA: Microsoft SQL Server 2012 (Querying)

by TechEd North America

the constraint should have been on SSN, but SSN + timestamp has the constraint...so you cna have duplicate SSN, but on different rows due to the tiemstamp.

there's several ways, you could use row number, or a select min(timestampcolumn),SSN group by SSN query. it's really something you should practice with and experience...you'll want to really be familiar with the different ways to do things like this.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Lowell (8/31/2015)
SQLBill (8/31/2015)
Trick question isn't it?
UNIQUE constraint: used to "...make sure that no duplicate values are entered..." So you can't have duplicates.
-SQLBill
no, it's a classic question on the 70-461 test. the prep video from Microsoft on you tube gives a decent example of this, if you care to watch it.
Exam Prep: 70-461 - MCSA: Microsoft SQL Server 2012 (Querying)
by TechEd North America
the constraint should have been on SSN, but SSN + timestamp has the constraint...so you cna have duplicate SSN, but on different rows due to the tiemstamp.
there's several ways, you could use row number, or a select min(timestampcolumn),SSN group by SSN query. it's really something you should practice with and experience...you'll want to really be familiar with the different ways to do things like this.

Ah, but that is different than what the OP said:

it has 2 columns SSN - unique column and timestamp

That says there are two columns:

SSN - unique column

and

timestamp

Which would make it a trick question and my answer would be correct. However, it wouldn't be correct for the scenario you described.

-SQLBill

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

Actually the OP's post is poorly worded.

SSN - unique column? Of course it has to be unique! SQL Server does not allow more than one column with the same column name!

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]