CODEWITHSUNDEEP
sqlsql-server
Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

SQLSERVER get last record from recordset

How to get only last record

strConnStr = "Provider=SQLNCLI11;..."
Set cn = server.CreateObject("ADODB.Connection")
    cn.Open strConnStr
    strSQL = "SELECT DISTINCT year1 FROM cars WHERE year1 > 1900 ORDER BY year1;" 
Set rs = Server.CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = cn
    rs.Open strSQL
    YearList = rs.GetRows()
    rs.MoveLast
    MaxYear = rs.Fields(0)
cn.Close 
set rs = Nothing
set cn = nothing    

response.write MaxYear

return nothing, also i tryed MaxYear = rs(0), MaxYear = rs("Year1") all return nothing

I do not need to change strSQL !!!

Also i get error on rs.movelast

Microsoft SQL Server Native Client 11.0 error '80040e24'

Rowset does not support fetching backward.

Upvotes: 0

Views: 626

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Based on the update:

Set rs.ActiveConnection = cn
    rs.Open strSQL
    YearList = rs.GetRows()
    MaxYear = YearList(0,UBound(YearList))
cn.Close

Older answer:

If you're insistent on not changing the SQL (and so forcing the server to do more work on data that you're just going to throw away), then you just need to move through the whole recordset:

rs.Open strSQL
while Not rs.EOF
  YearArr = rs.Fields(0)
  rs.MoveNext
Wend

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172408

You can use the TOP keyword to get the latest record like this:

SELECT TOP 1 year1 FROM cars WHERE year1 > 1900 
group by year1 
ORDER BY year1 desc;

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

You can use the TOP keyword to get the latest record desc like this

SELECT TOP 1 year1 FROM cars WHERE year1 > 1900 
group by year1 
ORDER BY year1 Desc;

Upvotes: 1

Related Questions