Sql to Output Line Number in Results of a Query

SQL to output line number in results of a query

It depends on the database you are using. One option that works for SQL Server, Oracle and MySQL:

SELECT ROW_NUMBER() OVER (ORDER BY SomeField) AS Row, *
FROM SomeTable

Change SomeField and SomeTable is according to your specific table and relevant field to order by. It is preferred that SomeField is unique in the context of the query, naturally.

In your case the query would be as follows (Faiz crafted such a query first):

SELECT ROW_NUMBER() OVER (ORDER BY client_name) AS row_number, client_name
FROM (SELECT DISTINCT client_name FROM deliveries) TempTable

I think it won't work for SQLite (if someone can correct me here I would be grateful), I'm not sure what's the alternative there.

Add a row number to result set of a SQL query

The typical pattern would be as follows, but you need to actually define how the ordering should be applied (since a table is, by definition, an unordered bag of rows). One way to do that if you don't care about a specific order otherwise is to use the leading key(s) of a covering index, the leading key(s) of the clustered index, or the columns in any group by / order by clauses. In this case I'll assume A is the single-column clustering key for t:

SELECT t.A, t.B, t.C, number = ROW_NUMBER() OVER (ORDER BY t.A)
  FROM dbo.tableZ AS t
  ORDER BY t.A;

If you truly don't care about order, you can generate arbitrary/nondeterministic row numbering using:

ROW_NUMBER() OVER (ORDER BY @@SPID)

-- or for serial plans

ROW_NUMBER() OVER (ORDER BY @@TRANCOUNT)

Little tricks I picked up from Paul White in this article (see "Paul's Solution").

Not sure what the variables in your question are supposed to represent (they don't match).

Count records in table and show line number of specific value

Please find the query with the window function:

DECLARE @intPos AS INT = 0;

;WITH RowCountCte AS (
   SELECT ID, ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS intPos
   FROM tblData
)

SELECT @intPos = intPos FROM RowCountCte WHERE ID = 101;

SELECT COUNT(ID) AS cntRec, @intPos AS intPos
FROM tblData;

Demo on db<>fiddle

SQL print line number in comment of dynamically created stored procedure?

You can use TRY / CATCH with a forced error as the CATCH block can return the line number that the error occurred on via the ERROR_LINE() function. The full construct, formatted for readability, is:

BEGIN TRY
    ;THROW 50000, 'Line#', 1 -- all 3 values are arbitrary, but required
END TRY
BEGIN CATCH
    SET @LineNumber = ERROR_LINE()
END CATCH

Now, to get the @LineNumber variable to populate with the line number that it is being set on, you can reduce that construct to a single line as follows:

BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH

Here is a full example of it working:

SET ANSI_NULLS ON
SET NOCOUNT ON
GO
-- Line #1 (of current batch, not of the entire script if GOs are used)

DECLARE @CRLF NCHAR(2) = NCHAR(13) + NCHAR(10),
        @SQL1 NVARCHAR(MAX) = '',
        @SQL2 NVARCHAR(MAX) = '', -- Line #5
        @Line INT = -1 -- default to an invalid line #

SET @SQL1 += N'/********************' + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' * Test Auto-' + @CRLF -- Line #10
SET @SQL1 += N' * Generated Proc 1' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL1 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL1 += N' *' + @CRLF
SET @SQL1 += N' ********************/' + @CRLF -- Line #15

-- more code here

SET @SQL2 += N'/********************' + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #20
SET @SQL2 += N' * Test Auto-' + @CRLF
SET @SQL2 += N' * Generated Proc 2' + @CRLF
BEGIN TRY;THROW 50000,'',1;END TRY BEGIN CATCH;SET @Line=ERROR_LINE();END CATCH
SET @SQL2 += N' * Line #:' + CONVERT(NVARCHAR(10), @Line) + @CRLF
SET @SQL2 += N' *' + @CRLF -- Line #25
SET @SQL2 += N' ********************/' + @CRLF

PRINT @SQL1
PRINT @SQL2
GO

The line numbers returned for Proc 1 and Proc 2 are 12 and 23 respectively, which is correct for both.

Please note that the THROW command started in SQL Server 2012. If you are using SQL Server 2005, 2008, or 2008 R2, then you need to use RAISERROR() function instead of THROW.

SQL query - split out count() results into one line of GROUP BY results

You can use SUM(CASE WHEN... to do this

SELECT Year, Month, SUM(CASE WHEN Colour = 'Blue' THEN 1 ELSE 0 END) AS Blue, etc
FROM table
GROUP BY Year, Month

How to get numbers of line with sql?

I think you are just one step away from do it

$sql ="SELECT id FROM times WHERE userid=8";
$query =mysqli_query($con,$sql); 

$mid = array();
while($row = mysqli_fetch_array($query)) {
    $mid[] = $row['id'];
}

print_r($mid);

Your required values are now stord in your array $mid

Upon op request i add some code

Now if you need to echo values you can loop in the array and print while iterating

foreach($mid as $val)
{
    echo $val . '<br />';
}
//print 
// 1
// 4 
// 5
// 8

Show only the first N lines of output of a SQL query

It would be helpful if you specify what database you are targetting. Different databases have different syntax and techniques to achieve this:

For example in Oracle you can ahieve this by putting condition on RowNum (select ... from ... where ... rownum < 11 -> would result in outputting first 10 records)

In MySQL you can use you can use limit clause.

Microsoft SQL Server => SELECT TOP 10 column FROM table

PostgreSQL and MySQL => SELECT column FROM table LIMIT 10

Oracle => select * from (SELECT column FROM table ) WHERE ROWNUM <= 10 (thanks to stili)

Sybase => SET rowcount 10 SELECT column FROM table

Firebird => SELECT FIRST 10 column FROM table

NOTE: Modern ORM tools such as Hibernate give high level API (Query, Restriction, Condition interfaces) that abstract the logic of top n rows based on the dialect you choose.

---

Related Topics