CODEWITHSUNDEEP
mysqlsqlgo
Chirag
Chirag

Reputation: 658

Access second row of MySQL query result in Go

I am running a MySQL query in Go. I want access the second row of the query result. I know I can use

for rows.Next {

}

But I don't want to run a loop for accessing the second row (and then breaking the loop after it iterates the second time). What to do?

Here is a code snippet:

rows,err:= db.Query("SELECT status,ts FROM events WHERE node = ? order by ts desc limit 2", testNode.ID);
defer rows.Close()
if ( err!= nil){
    t.Error("Some Error" + err.Error())
}
isNext:=rows.Next()
if(isNext == false) {
    t.Error(" No rows in query result")
}
rows.Scan(&status)
// What to do to use second row ?

Upvotes: 1

Views: 956

Answers (1)

icza
icza

Reputation: 418585

Sticking to DB.Query()

If you're going to discard the first row, there is no point in retrieving it from the database.

Use LIMIT 1, 1 to discard the first result and limit the result to 1 row (check out the doc of LIMIT at: MySQL SELECT syntax). Then simply proceed reading the first row which will be the 2nd row of your query result:

q := "SELECT status, ts FROM events WHERE node = ? order by ts desc limit 1, 1"
rows, err := db.Query(query, testNode.ID);
if err != nil {
    t.Error("Error:", err)
    return
}
defer rows.Close()

if !rows.Next() {
    t.Error("No result")
    return
}
if err := rows.Scan(&status); err != nil {
    t.Error("Failed to scan:", err)
    return
}

// All good, use status
fmt.Println("Status:", status)

More examples using LIMIT:

SELECT * FROM tbl LIMIT 5,10;  # Retrieve rows 6-15
SELECT * FROM tbl LIMIT 5;     # Retrieve first 5 rows

Using to DB.QueryRow()

If you're expecting at most 1 row, you may also use DB.QueryRow() and the result will be much more compact:

q := "SELECT status, ts FROM events WHERE node = ? order by ts desc limit 1, 1"
if err := db.QueryRow(query, testNode.ID).Scan(&status); err != nil {
    t.Error("Failed to scan, no result?")
    return
}

// All good, use status
fmt.Println("Status:", status)

Upvotes: 1

Related Questions