| Author |
 Topic  |
|
|
StephenD
Senior Member
Australia
1044 Posts |
 Posted - 12 April 2007 : 06:31:05
|
Is there a way to return the 2nd last row in a table? I can return the last row ok:
strSql = "SELECT T.TOPIC_ID, B.T_SUBJECT "
strSql = strSql & "FROM " & strTablePrefix & "CRIMTRAC_AUDIT T, " & strTablePrefix & "TOPICS B "
strSql = strSql & " WHERE T.TOPIC_ID = B.TOPIC_ID "
strSql = strSql & " AND (SELECT TOP 1 actionIndicator FROM FORUM_CRIMTRAC_AUDIT WHERE FORUM_CRIMTRAC_AUDIT.TOPIC_ID = T.TOPIC_ID ORDER BY FORUM_CRIMTRAC_AUDIT.auditID DESC ) = 5 "
strSql = strSql & " GROUP BY B.T_SUBJECT, T.TOPIC_ID ORDER BY B.T_SUBJECT, T.TOPIC_ID ASC " |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 12 April 2007 : 07:05:25
|
Hmm ... try the following; seem to remember using something along these lines once many moons ago:... AND (SELECT TOP 1 actionIndicator FROM FORUM_CRIMTRAC_AUDIT WHERE FORUM_CRIMTRAC_AUDIT.TOPIC_ID = T.TOPIC_ID AND actionIndicator<>(SELECT TOP 1 actionIndicator FROM FORUM_CRIMTRAC_AUDIT WHERE FORUM_CRIMTRAC_AUDIT.TOPIC_ID = T.TOPIC_ID ORDER BY FORUM_CRIMTRAC_AUDIT.auditID DESC) ORDER BY FORUM_CRIMTRAC_AUDIT.auditID DESC ) = 5 ... I'd recommend testing that carefully first, just in case it creates an infinite loop 
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 12 April 2007 : 07:41:52
|
| Hmmm .. getting an incorrect syntax near one of the 'ORDER' clauses. Might have to think about an easier way to do this. |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 12 April 2007 : 07:46:41
|
Try changing the <> to NOT IN, before you give up on this route
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 12 April 2007 : 07:50:41
|
| Ah, tried that and got the same error. Sorry mate, can't test any more tonight gotta go catch the last ferry. Thanks for all your help. |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 12 April 2007 : 07:54:37
|
No worries, I'll see if I can get over this insomnia tonight and look at it with fresh eyes for you tomorrow.
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 12 April 2007 : 21:37:29
|
| Went with a different approach and got the information I needed a different way thanks. |
|
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 13 April 2007 : 04:33:45
|
Cool; glad you found a solution 
|
Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
|
|
|
pdrg
Support Moderator
United Kingdom
2897 Posts |
Posted - 13 April 2007 : 12:37:42
|
Pure pedantry here, but there is no such thing as first and last rows of a table - you have to assume the dbengine will write rows into its pages in whatever order it feels is best (which may depend a bit on the developer), but that it will return them in the fastest way it knows how. If you have a single processor and single disk, you're almost certainly going to get the data in the same order each time, but if you've got mirrored disks, or multi-processors, a good dbengine will give you the data in all kinds of orders unless you specifically ask the data to be ordered (as ordering takes work, so it'll only do that work if asked!)
hth :) |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 13 April 2007 : 17:05:38
|
quote:
Pure pedantry here, ...
Pure accuracy, I'd say!
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 13 April 2007 : 17:21:34
|
Ah, semantics. I'm ready to leave the office and start some antics now.  Time to go to the gym, work out, and flirt with the cute red-headed lifeguard.  Too bad I don't seem to be getting anywhere.... |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 14 April 2007 : 18:11:42
|
Once you've answered enough "how do i select the last entry" questions, defining how db tables work is not semantics. It's commonly misunderstood, people tend to think the db has some built-in ordering of data, but that's incorrect.
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 14 April 2007 : 20:33:49
|
| Guess I just forgot to add my </sarcasm> tag. I won't forget next time. |
|
|
|
Doug G
Support Moderator
USA
6493 Posts |
Posted - 16 April 2007 : 01:52:39
|
quote:
Originally posted by AnonJr
Guess I just forgot to add my </sarcasm> tag. I won't forget next time.
Not to worry. I enjoyed your comment!
I just felt like throwing in some more about the inherent un-orderdness of tables in databases as a general comment, not anything to do with you. Oh, and good luck in getting acquainted with the lifeguard [:D}
|
======
Doug G
======
Computer history and help at www.dougscode.com |
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 16 April 2007 : 06:50:33
|
quote:
Originally posted by Doug G
Oh, and good luck in getting acquainted with the lifeguard
Heh, the way this is going I may have to start a new topic in the "Moral Support" forum...  but that would be getting further off topic.
As to commentary regarding the confusion over the orderliness of a database, I think what makes it such a common misunderstanding is that you usually have some sort of an ID field, and most RDBMSs will sort on that by default - giving you a record #1, and a record #5, and the last record entered.
It probably doesn't help when the record set gets confused for the underlying data its being pulled from - usually your record sets have some sort of order to them, and usually they have a first and last row.
Because of the way these things work (and probably some other stuff too), its easy to fall into the mindset that there is an order with a 1st and last record.
But that may just be me rambling before the first cup of coffee.  |
|
|
|
StephenD
Senior Member
Australia
1044 Posts |
Posted - 16 April 2007 : 07:03:30
|
| Maybe I should have mentioned that my field AuditID is the Primary key with seed/auto-increment. |
|
|
| |
Topic |
|
Topic Locked
