SQL query last item
fredmjf | Posted 3:51pm 1. February 2008 Server Time |
Hello,
I have the following question:
To get the latest item i use the folowing code:
sSQL = "SELECT TOP 1 * FROM TblNews Order by Date DESC"
But now i need not the last item but the one just before that one!
Does anyone know the code as simple as possible please...
Thanks,
Fred
|
enigma | Posted 10:36am 3. February 2008 Server Time |
fredmjf,
SELECT TOP 1 *
FROM
(
SELECT TOP 2 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
Cheers,
enigma
fredmjf | Posted 1:24pm 3. February 2008 Server Time |
Hi Enigma!
Thanks; it works!!
As i don't understand any of the code; do you know then also how i can get the second last item?
What does tbl000 do and mean? and ASC as in 'ORDER BY Date ASC'?
Thanks so far
greetings,
Fred
blakord | Posted 2:56am 5. February 2008 Server Time |
I think you can't, you must use standar query for read all items using "ORDER BY Date DESC" and limit to last 2 only with a counter condition into the loop for read just 2 times
Condition = 0
While NOT rs.EOF and Condition < 2
Condition = Condition + 1
'Read field here
Wend
enigma | Posted 8:00am 5. February 2008 Server Time |
blakord & fremjf,
Can't is not in the programmer's dictionary.
Third-to-last item:
SELECT TOP 1 *
FROM
(
SELECT TOP 3 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
Third and second to last-item:
SELECT TOP 2 *
FROM
(
SELECT TOP 3 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
"tbl000" is an alias name (needed for proper ANSI-92 SQL standard) to the subquery that is run first. "ORDER BY Date ASC" means order by Date field value ASCending and likewise, DESC means DESCending.
Cheers,
enigma
fredmjf | Posted 9:03am 5. February 2008 Server Time |
You are great Enigma!
Have i got one last question:
On my news page i'll show the first, the second and the third item separatly. Later on the page i'll show the ten latest newsitems that where not 1,2 and 3.
So 4 to 13.
Do you know by any chance how i can do that?
Somethuing like:
sSQL = "SELECT TOP 13 * FROM TblNews Order by Date DESC"
But how can i skipp the three first ones??
Hope you can help me for the last time also.
Thanxs so far,
Fred
blakord | Posted 10:26am 5. February 2008 Server Time |
enigma Said
Can't is not in the programmer's dictionary.
Heh agree, I hope you right, plz check my question
http://www.haneng.com/Forums_Post.asp?id=13278&p=1
Alway you can read the dBase sequential, you can only select items using "WHERE <CONDITION>" clause, if before read the dBase you don't know the condition, then you must read all sequential, and use "if" into the loop for select what items are valid for you, example
Counter = 1
While NOT rs.EOF
If Counter > 3 Then
' Do This
End If
Counter = Counter + 1
Wend
enigma | Posted 12:39am 5. February 2008 Server Time |
fredmjf,
Two pages, two SQL calls.
The first (as you might have guessed):
SELECT TOP 3 *
FROM
(
SELECT TOP 13 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
The second, just ensure to get the last 10 records back in ascending order (thus you request of last 4-13 records which in reality is last 10):
SELECT *
FROM
(
SELECT TOP 10 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
Cheers,
enigma
enigma | Posted 12:44am 5. February 2008 Server Time |
blakord,
I have answered your haneng post with URL http://www.haneng.com/Forums_Post.asp?id=13278&p=1
Cheers,
enigma
fredmjf | Posted 1:38pm 5. February 2008 Server Time |
Enigma,
The last query got the right answers for me but is it anyway possibly to get them in order from latest to oldest instead of the oldest on top now?
SELECT *
FROM
(
SELECT TOP 10 *
FROM TblNews
ORDER BY Date DESC
) AS tbl000
ORDER BY Date ASC
fredmjf | Posted 1:49pm 5. February 2008 Server Time |
I think there is a simpeler query but this one works:
"SELECT TOP 10 * FROM (SELECT TOP 10 * FROM (SELECT TOP 13 * FROM TblNews ORDER BY Date DESC) AS tbl000 ORDER BY Date ASC) AS tbl000 ORDER BY Date DESC"
Thanks Enigma for your work!
enigma | Posted 3:12pm 5. February 2008 Server Time |
fredmjf,
If that is the case then there is no need to use a subquery for the second part, just:
SELECT TOP 10 *
FROM TblNews
ORDER BY Date DESC
... and you are done.
Cheers,
enigma
Reply to Post SQL query last item
|
|
|
