Menu
We're using PostgreSQL v8.2.3.
How do I generate serial number in the query output? I want to display serial number for each row returned by the query.
Example: SELECT employeeid, name FROM employee
I expect to generate and display serial number against each row starting from one.
530
In SQL Server 2000 you can do SELECT ID=identity(int,1,1),* INTO #temp FROM........................ You can try this, using row_number() function. Assuming your query result come from union of table A and B. SELECT row_number() over (order by X.
The syntax to create a sequence in SQL Server (Transact-SQL) is: CREATE SEQUENCE [schema.] sequence_name [ AS datatype ] [ START WITH value ] [ INCREMENT BY value ] [ MINVALUE value | NO MINVALUE ] [ MAXVALUE value | NO MAXVALUE ] [ CYCLE | NO CYCLE ] [ CACHE value | NO CACHE ]; AS datatype.
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .
mysql> SELECT @serialNumber − = @serialNumber+1 yourSerialNumber, -> StudentName,StudentAge,StudentMathMarks from tblStudentInformation, -> (select @serialNumber − = 0) as serialNumber; Here is the output displaying the row number in the form of serial number.
You have two options.
Either upgrade to PostgreSQL v8.4 and use the row_number() function:
SELECT row_number() over (ORDER BY something) as num_by_something, *
FROM table
ORDER BY something;
Or jump through some hoops as described in Simulating Row Number in PostgreSQL Pre 8.4.
189
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
