Getting Last Record and First Record from a Record Set (SQL server DB)
-
I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column. This is my procedure:
$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$connectionInfo = array( "Database"=>$myDB, UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last = $reg['column_name'];It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT? Thank you in advance.
-
I am pretty new using MS SQL. I perform a query over some SQL server table; it returns a record set; but I need to read the last record just to get the value of a certain column. This is my procedure:
$connectionInfo = array( "Database"=>$myDB, "UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, $connectionInfo);$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));
$connectionInfo = array( "Database"=>$myDB, UID"=>$myUser , "PWD"=>$myPass);
$conn = sqlsrv_connect( $myServer, connectionInfo);$query = "SELECT * FROM( SELECT col0, col1, col2, col3 FROM t1 LEFT JOIN t2 ON t1.col1 = t2.col1 LEFT JOIN t3 ON t1.col2 = t3.col2) AS t ORDER BY t.col0; ";
$rec_set = sqlsrv_query($connection, $query, array(), array("Scrollable" => 'buffered'));
if( $rec_set === false ) die( print_r( sqlsrv_errors(), true));$row_count = sqlsrv_num_rows($rec_set);
if ($row_count === false) die( print_r( sqlsrv_errors(), true));$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, 0);
$first = $reg['column_name'];$reg = sqlsrv_fetch_array( $rec_set, SQLSRV_FETCH_ASSOC, $row_count - 1);
$last = $reg['column_name'];It is clear that the sqlsrv_fetch_array DOES NOT work like that. Does anyone know how to achieve my goal, that is, getting last and first records from that record set without generating another SELECT? Thank you in advance.
Have you tried:
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1
and then
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 DESC
The difficult we do right away... ...the impossible takes slightly longer.
-
Have you tried:
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1
and then
SELECT TOP 1 COL1 FROM TABLE ORDER BY COL1 DESC
The difficult we do right away... ...the impossible takes slightly longer.
No. The problem here is that I have worked with mysql/postresql and I can retrieve from a recordset any record I want depending on its position in the recordset. So when you have: $var = pg_fetch_result($recordset, $k, 'col'); you are retrieving the 'col' value in the k-th record from that recordset (in PostgreSQL). You can do the same in MySQL. I want to achieve the same in sql server... I have noticed this parameter en the fetch of sql: SQLSRV_SCROLL_LAST in the row parameter of the sqlsrv_fetch_array(resource $stmt, int $fetchType = ?, int $row = ?, int $offset = ?). But for some reason I can not make it run.
-
No. The problem here is that I have worked with mysql/postresql and I can retrieve from a recordset any record I want depending on its position in the recordset. So when you have: $var = pg_fetch_result($recordset, $k, 'col'); you are retrieving the 'col' value in the k-th record from that recordset (in PostgreSQL). You can do the same in MySQL. I want to achieve the same in sql server... I have noticed this parameter en the fetch of sql: SQLSRV_SCROLL_LAST in the row parameter of the sqlsrv_fetch_array(resource $stmt, int $fetchType = ?, int $row = ?, int $offset = ?). But for some reason I can not make it run.
Add a "row number"? [ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver16#general-remarks)
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
