| Author |
Topic |
|
sbt1
Yak Posting Veteran
89 Posts |
 Posted - 2003-09-30 : 08:08:08
|
| I'm writing a database browser, to let my users view (not change) the data in my app's tables.I need to be able to give the users the option of retrieving the oldest x records or newest x records (they select how many to read and display).I know how to retrieve the oldest records (SELECT TOP x * FROM...) but how to get the most recent ones? To my knowledge there isn't a BOTTOM compliment to TOP.The browser app doesn't know anything about the structure of any of the tables, so I can't do it that way.Any ideas? |
|
|
dsdeming
479 Posts |
Posted - 2003-09-30 : 08:15:09
|
| SELECT TOP requires the use of an ORDER BY clause. If ORDER BY ASC ( the default ) returns the oldest, ORDER BY DESC will return the newest.Dennis |
 |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-30 : 09:57:33
|
| I'm begining to hate TOP....It's so undeterministic...even with an ORDER BYAdd "Data Administrative" columns to your tables...Things like when the row was added, updated, (logically) deleted, by whom, maybe what type of DML affected the row...Then you can then say this was the first row added...this was the last...this was the avg number of inserts per day...this record is the 1 in the middle...yada yada yadaMOOBrett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-09-30 : 11:36:49
|
Yeah, I could add "Data Admin" columns if they were my tables... but this app needs to read any tables, not just ones I've had the luxury of creating or specifying fields for.And the problem with the ORDER BY is that my application doesn't know anything about the fields, so it has no way of knowing what to sort by.Hmmm. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-09-30 : 12:15:59
|
| Sounds like you must add a CreationStamp field to all your tables if you want the oldest/newest record.__________________Make love not war! |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-09-30 : 12:20:09
|
| Again, that would be great and easy to do if I happened to be the guy creating the tables. But I'm not, so I can't do that. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-09-30 : 13:39:37
|
| Do you know how many rows are in each table?How does a browser work with data that doesn't know it's structure?What if you have a 300 column table?What if you have 50 million rows of data...Who is the audiance for the browser?Have you looked at sp_makewebtask?Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-10-01 : 06:42:39
|
| I'm using metadata queries to determine the table names and the field names in each table. That way I get a field count, set up a grid control to display the data, then insert the data from each field into the grid as I run thru the recordset. Works fine, and the app has no idea what any of the fields or tables are for or how they're set up, which is exactly how I want to develop this app.Haven't heard of sp_makewebtask, what does it do? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-01 : 09:23:23
|
| sp_makewebtask produces html documents based on a query(ies) against the database....just like what you're trying to do...stores the info in an html file that you define...look it up in books online...I created a script that generated them for every table in a database for the QA group...can't find right now...it's been a while...and it was in 7...Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-10-02 : 07:41:33
|
| Still, using either my method or the makewebtask method, I still need a way to get the "most recent X records" from tables.Why isn't there a way to write "SELECT NEWEST 1000 * FROM..." :-)Surely others must need to run this type of query. |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-02 : 09:04:57
|
| There is....put a LASTINSERTED DATE/TIME value on each record....or else IF the table has an identity value as one field....look for the TOP x RECORDS ORDER BY identifyfield DESC.Your problem is that you want to do a search using some non-computerised information.....ie...you 'know' what records were the last X records inserted into the database because you typed them in, but no information (date/timestamp) is getting saved in the database to allow such a computerised search....these are incompatible objectives. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-10-02 : 09:17:49
|
| How about an insert trigger on all your tables?You could create a separate table to hold a TimeStamp which stores the time a record is inserted. You could then query your new table to find out the newest/oldest records.__________________Make love not war! |
|
|
|
drymchaser
Aged Yak Warrior
552 Posts |
Posted - 2003-10-02 : 09:19:29
|
quote:
Originally posted by sbt1Why isn't there a way to write "SELECT NEWEST 1000 * FROM..." :-)
There is a way, multiple ways in fact, you have been given many in this thread. However, the rows themselves must contain this information. |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-10-03 : 09:03:14
|
| Thanks for your suggestions, but unfortunately :-) it seems I didn't explain this fully enough: the application I'm writing is a GENERIC browser... it has NO WAY to know ANYTHING about any of the tables! This is because it needs to view tables created by anyone, not just me.So there is *no* way to put any kind of timestamp or any other information inside a table, to do what I'm trying to do.*Sigh* |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2003-10-03 : 09:29:00
|
yea....it's a real bummer.....I'd like MS-SQL to cook, clean, drive-me to work, pick the winning lotto numbers and buy me beer  ........and it's just not good enough!!!!maybe that means there's a market opportunity for mind-reading software?.... |
|
|
|
sbt1
Yak Posting Veteran
89 Posts |
Posted - 2003-10-03 : 14:52:16
|
| Ok, found the solution. It's incredibly un-elegant, but works.If I want the newest (most-recently-added) X rows, I read all the rows, then move to the last record and back up X times, writing the field data to my grid as I go.Slower than reading the oldest X records but it does work. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-03 : 15:01:12
|
quote:
Originally posted by sbt1
Ok, found the solution. It's incredibly un-elegant, but works.If I want the newest (most-recently-added) X rows, I read all the rows, then move to the last record and back up X times, writing the field data to my grid as I go.Slower than reading the oldest X records but it does work.
Well as long as it works for you...but understand...the order of data in a relational database has no meaning...AND, you could run the same process over and over and get different results...Good luck...Really curious though...who's the audiance for this AppBrett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-10-03 : 16:01:16
|
| Unless you use ORDER BY, your data is a box of chocolates. You never know what you're gonna get!Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-10-06 : 11:08:08
|
quote:
Originally posted by MichaelP
Unless you use ORDER BY, your data is a box of chocolates. You never know what you're gonna get!<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
your data is like a box of.....GREAT movie....Brett8-)SELECT @@POST FROM Brain ORDER BY NewId()That's correct! It's an AlphaNumeric! |
|
|
|
|
How to select newest-added records from a table