Please help

dinesh · February 6th, 2009, 09:30 AM

ok i will try to do it tmrro. gud luck frnd.

Jeff Moden · February 6th, 2009, 11:28 AM

Quote:

Originally Posted by dinesh

you mean "any two" rite, why dont it be the first two?

Because without an ORDER BY, there is no guarantee as to what the order of rows will be in SQL Server. Even a clustered index doesn't guarantee the order of the return on SELECTs.

Old Pedant · February 6th, 2009, 04:24 PM

For what it is worth, you need to use ORDER BY in *both* the SELECTs that Jeff shows, if you want to control the ordering of the records:

Code:

SELECT *
FROM Departments d1
WHERE EmpName IN ( SELECT TOP 2 EmpName 
                   FROM Departments d2 
                   WHERE d2.DepartmentID = d1.DepartmentID
                   ORDER BY d2.NumberOfKumquatsEaten )
ORDER BY d1.departmentID, NumberOfKumquatsEaten

The field "NumberOfKumquatsEaten" is of course nonsense. Choose a real field that makes sense in your DB/Table. The point is, though, that you *MUST* control the ordering YOURSELF. SQL won't do it for you.

Jeff Moden · February 6th, 2009, 07:59 PM

The problem with the original problem posted is that OP didn't stipulate anything to sort on.

Old Pedant · February 6th, 2009, 08:27 PM

Sure. So don't you think NumberOfKumquatsEaten is as good a guess as any? <grin style="foolish" />

Jeff Moden · February 6th, 2009, 08:33 PM

Heh... I have to admit, it's close.