3

I have a table called STUDENT

+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName  | EnrollmenDate |
+-----------+-----------+-----------+---------------+
|         1 | x         | x         | x             |
|         2 | x         | x         | x             |
|         3 | x         | x         | x             |
+-----------+-----------+-----------+---------------+

Write a query that pulls the student who registered the last?

a) select top 1 * from STUDENTorder by EnrollmentDate Desc

or

b) select * from STUDENT where EnrollmentDate = (select Max(EnrollmentDate) from STUDENT)

I tend to ask this question in interview. One candidate answered b) I was expecting a). Which one is better query?

Improve this question
8
  • 2
    Can't you test it? I think your question is much more useful if phrased as "how I can test which of these queries is more efficient?"
    – Aaron Bertrand
    Aug 13, 2012 at 18:56
  • How can I test it? I mean they give the same result and both are accurate. But which one is better from performance point of view.
    – TheTechGuy
    Aug 13, 2012 at 18:57
  • Definitively stick to the first option. The second option needs another scan / seek to the table depending on your index to find the MAX value which is unnecessary.
    – Mentor
    Aug 13, 2012 at 18:57
  • 5
    You might be better off if you only ask interview questions about technologies you are proficient with based on your real life experience. Otherwise you are very likely to have some false positives and false negatives.
    – A-K
    Aug 13, 2012 at 19:05
  • 2
    Has anyone ever asked you how to handle ties? Have you considered the following: select top(1) with ties * from STUDENT order by EnrollmentDate Desc
    – A-K
    Aug 13, 2012 at 19:38

1 Answer 1

Reset to default
7

Write a query that pulls the student who registered the last?

The second query does not necessarily do this, depending on the data type of the EnrolmentDate column and how granular the data is. If this column does not contain a time component, the query will return all students registered on the last day a student registered, which does not satisfy the question. If there is a time component, it's possible (but much less likely) that there will be multiple rows returned.

(Edit: Alex Kuznetsov correctly pointed out in the comments that the first query doesn't necessarily return the last enrolled student either when there are ties. It is, however, guaranteed to return a record in that event, instead of all records, which is normally satisfactory. I think my point was more that comparing the two queries is comparing apples to oranges, so to speak.)

In any event, if we assume all enrollment dates/times are unique, from what's given, the answer to the question isn't necessarily clear cut either. You would need to qualify for me what you mean by more efficient.

The first query will only scan once, but could potentially incur an expensive sort (you didn't say which indexes exist on the table, so I assume none). The latter query will do a scan to find the maximum, then do another scan to find all matching rows, which would possibly use less CPU, but more logical I/Os. It's entirely possible the second query would be less expensive overall (again, with no indexes available).

Having said all that, if I was to start doing performance tuning on this business operation, I would most certainly start with query (a).

Improve this answer
3
  • I agree with you and I would say that if all he is getting as an answer is one of the two options without the types of caveats you have here, he is clearly only interviewing people who have a poor understanding of databases.
    – HLGEM
    Aug 13, 2012 at 22:06
  • @HLGEM: "he is clearly only interviewing people who have a poor understanding of databases" -- I'm not sure I can agree with that: there really isn't enough information given to say. The OP stated that "one candidate answered (b)" but didn't say how many others answered with (a), or what kind of discussion ensued. As an interviewer, it would be much more informative to probe deeper to ask why, regardless of which answer was chosen -- I think that would be much more revealing than just a simple multiple-choice question.
    – Jon Seigel
    Aug 14, 2012 at 2:27
  • 1
    (...) In that respect, it's actually quite an interesting question to ask, even if it wasn't intended that way.
    – Jon Seigel
    Aug 14, 2012 at 2:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.