Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to make SELECT NEXT VALUE work in SQL Server?

I'm having some difficulties trying to get the following to work in SQL Server:

CREATE TABLE table_X AS
    SELECT 
        NEXT VALUE FOR GROUP_A AS GROUP_ID, RISK_ID
    FROM 
        ( SELECT UNIQUE RISK_ID, FROM table_Y ) SS;

I am told:

"Incorrect syntax near the keyword 'SELECT'."
"Incorrect syntax near the keyword 'UNIQUE'."

What exactly is the problem and how can I fix this to work in SQL Server?

like image 978
Alexander Kalian Avatar asked Aug 30 '13 11:08

Alexander Kalian


People also ask

How do I select next value in SQL?

SELECT or INSERT ... EXEC statement where the data being inserted comes from a query using an ORDER BY clause, the values being returned by the NEXT VALUE FOR function will be generated in the order specified by the ORDER BY clause.

How do I find the next row in SQL?

SQL Server LEAD() is a window function that provides access to a row at a specified physical offset which follows the current row. For example, by using the LEAD() function, from the current row, you can access data of the next row, or the row after the next row, and so on.

How do you use values from previous or next rows in a SQL Server query?

Overview of SQL Server LAG() function In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on. The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.


1 Answers

CREATE TABLE Person
(
FirstName Varchar(50),
LastName Varchar(50)
);

/*
Insert Some Values Into Person
*/

CREATE SEQUENCE CountBy
    START WITH 1
    INCREMENT BY 1 ;

SELECT NEXT VALUE FOR CountBy OVER (ORDER BY LastName) AS ListNumber,
    FirstName, LastName
    INTO table_x
FROM Person
like image 175
Sridhar Avatar answered Nov 10 '22 07:11

Sridhar