Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
 Posted - 2004-09-15 : 14:51:47
|
| How would I write a query to return rows 20 - 30 as they were entered into the system, the table has no indexes and does not contain date time stamps. Interviewer suggested Using Top not in Top logic...I went home and tried to implement this kind of logic but could not get it to generate results... Select top 10 * from sysobjects t, (Select top 30 * from sysobjects) m Where t.id = m.idHowever this statement returns the same result? What am I missingSelect top 10 * from sysobjects tI mentioned to the interviewer this would work if their were no indexes but could it would with or without?got to be something simple... If I can get my brain arround it.Surf On Dude! |
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-15 : 15:40:46
|
| Tried this as well:No Records returned...Select top 10 * from sysobjects t Inner Join (Select top 30 * from sysobjects Order by ID) m On t.ID=m.ID Where t.id <> m.id Order by t.ID Should I put this in the query form?Surf On Dude! |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-09-15 : 15:54:10
|
| I am not aware of any means to SELECT data and display it in the order it was entered (unless you also have a datetime field present, or have a clustered index on an identity field). A simple answer to this interviewer is that you cannot do it.-ec |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2004-09-15 : 15:58:34
|
| select top 10 * from pubs..authorswhere au_id not in ( select top 19 au_id from pubs..authors)IMHO - the interviewer is a fool. Without a mechanism to guarantee order, the results of this are meaningless. |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-15 : 16:39:57
|
| I responded that I would normally create tables with system generated modify and append dates and told him that if the table had a clustered indexed this would mask any natural order for the records on the table.Was I correct on this part?Surf On Dude! |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-09-15 : 16:42:41
|
it sounds like you know a lot more than your interviewer does about relational databases to me. - Jeff |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2004-09-15 : 16:46:19
|
| Guess that's why they are looking to fill the position.Thk for the complement... As it turns out the guy asked his manager and the manager confirmed what I was saying as correct, not knowing their depth in this area I wanted to confirm this information thank you!!!Surf On Dude! |
|
|
|
|
|
|
Interview Question