SQL Select 'N' Records Without a Table

SQL Select 'n' records without a Table

You can use a recursive CTE to generate an arbitrary sequence of numbers in T-SQL like so:

DECLARE @start INT = 1;
DECLARE @end INT = 10;

WITH numbers AS (
    SELECT @start AS number
    UNION ALL
    SELECT number + 1 
    FROM  numbers
    WHERE number < @end
)
SELECT *
FROM numbers
OPTION (MAXRECURSION 0);

SQL Select 'n' records without a Table

You can use a recursive CTE to generate an arbitrary sequence of numbers in T-SQL like so:

DECLARE @start INT = 1;
DECLARE @end INT = 10;

WITH numbers AS (
    SELECT @start AS number
    UNION ALL
    SELECT number + 1 
    FROM  numbers
    WHERE number < @end
)
SELECT *
FROM numbers
OPTION (MAXRECURSION 0);

How to return N records in a SELECT statement without a table

The formula (10-COUNT(*)%10)%10 tells you how many rows to add, so you can just select that many dummy rows from an existing dummy table.

SELECT nacha_rows
FROM NACHA_TABLE
UNION ALL
SELECT TOP (SELECT (10-COUNT(*)%10)%10 FROM NACHA_TABLE) REPLICATE('9',94)
FROM master.dbo.spt_values

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

Is it possible to create many rows on a select statement without table?

Most databases provide a way to generate 1..n rows of data (see: SQL SELECT to get the first N positive integers) but MySQL doesn't make it easy. If you have a table that you know has enough rows to meet your requirements you can use that as a basis for a query to get what you want.

For example this will get you 10 rows:

SELECT @N := @N +1 AS rownumber
     ,floor(1+(rand()*(1+100-1))) as B1
     ,floor(1+(rand()*(1+100-1))) as B2
     ,floor(1+(rand()*(1+100-1))) as B3
     ,floor(1+(rand()*(1+100-1))) as B4
     ,floor(1+(rand()*(1+100-1))) as B5
     ,floor(1+(rand()*(1+100-1))) as B6 
FROM INFORMATION_SCHEMA.COLUMNS, (SELECT @N:=0) dummyRowNums LIMIT 10;

You can use any table to achieve this as long as you can be sure it has more rows than the LIMIT you're looking to reach.

If you don't need a rownumber in your result set, you can remove the first column in the SELECT and can remove the join (", (SELECT @N:=0) dummyRowNums"):

SELECT floor(1+(rand()*(1+100-1))) as B1
     ,floor(1+(rand()*(1+100-1))) as B2
     ,floor(1+(rand()*(1+100-1))) as B3
     ,floor(1+(rand()*(1+100-1))) as B4
     ,floor(1+(rand()*(1+100-1))) as B5
     ,floor(1+(rand()*(1+100-1))) as B6 
FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10;

How to select rows with no matching entry in another table?

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

How to skip the first n rows in sql query

Query: in sql-server

DECLARE @N INT = 5 --Any random number

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
              , ID --Add any fields needed here (or replace ID by *)
        FROM TABLE_NAME
) AS tbl 
WHERE @N < RoNum
ORDER BY tbl.ID

This will give rows of Table, where rownumber is starting from @N + 1.

SQL Server SELECT LAST N Rows

You can do it by using the ROW NUMBER BY PARTITION Feature also. A great example can be found here:

I am using the Orders table of the Northwind database... Now let us retrieve the Last 5 orders placed by Employee 5:

SELECT ORDERID, CUSTOMERID, OrderDate
FROM
(
    SELECT ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC) AS OrderedDate,*
    FROM Orders
) as ordlist

WHERE ordlist.EmployeeID = 5
AND ordlist.OrderedDate <= 5

lock and get next n records from table in oracle

To resolve this situation you need to use a for update cursor, because a for update clause is evaluated after the where:

| PLAN_TABLE_OUTPUT                                                                                    |
| :--------------------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT:                                                                             |
| ------------------------                                                                             |
| select /*+gather_plan_statistics*/ * from t where   decode(status, 'O',                              |
| id) is not null   and rownum < 10 order by decode(status, 'O', id) asc                               |
| for update skip locked                                                                               |
|                                                                                                      |
| Plan hash value: 2984481354                                                                          |
|                                                                                                      |
| ---------------------------------------------------------------------------------------------------- |
| | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | |
| ---------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT              |          |      1 |        |      9 |00:00:00.01 |      21 | |
| |   1 |  FOR UPDATE                   |          |      1 |        |      9 |00:00:00.01 |      21 | |
| |*  2 |   COUNT STOPKEY               |          |      1 |        |      9 |00:00:00.01 |      10 | |
| |   3 |    TABLE ACCESS BY INDEX ROWID| T        |      1 |     49 |      9 |00:00:00.01 |      10 | |
| |*  4 |     INDEX FULL SCAN           | IX_SFU_T |      1 |     10 |      9 |00:00:00.01 |       1 | |
| ---------------------------------------------------------------------------------------------------- |
|                                                                                                      |
| Predicate Information (identified by operation id):                                                  |
| ---------------------------------------------------                                                  |
|                                                                                                      |
|    2 - filter(ROWNUM<10)                                                                             |
|    4 - filter("T"."SYS_NC00005$" IS NOT NULL)                                                        |
|                                                                                                      |

But for explicit cursor with for update skip locked you lock only fetched rows and no rownum evaluation exists in the query: you limit the number of locked rows with limit clause of fetch.

create procedure l(s int)
as
  pragma autonomous_transaction;
  type t_tab is table of t%rowtype
    index by pls_integer;
  t_data t_tab;
  i pls_integer;
  
  cursor c is
  select *
  from t
  where
    decode(status, 'O', id) is not null
  order by decode(status, 'O', id) asc
  for update
  skip locked;
begin
  
  open c;
  fetch c
    bulk collect into t_data
    limit 5
  ;
  /*Do stuff*/
  commit;
  close c;
end;
/

Additionally, to improve the query performance you can use index on expression that evaluates to null on the cases out of interest (where status != 'O' in your case). In the upper query I've used decodewith the id column as output to assign rows in some predefined order (to use ordering on the same expression that was indexed to avoid table sorting), but if you do not need any order, index expression can be decode(status, 'O', status).

Full setup is below. db<>fiddle here

create table t
as
select level as id,
  dbms_random.string('x', 10)
   as val,
  dbms_random.random() as q,
  decode(mod(level, 20), 0, 'O', 'U')
    as status
from dual
connect by level < 1000

/*We will use this expression
for filtering and sorting*/
create index ix_sfu_t
on t(decode(status, 'O', id))

begin
dbms_stats.gather_table_stats(
  sys_context('USERENV', 'CURRENT_SCHEMA'),
  'T',
  cascade => true
);
end;
/

select /*+gather_plan_statistics*/ *
from t
where
  decode(status, 'O', id) is not null
  and rownum < 5
order by decode(status, 'O', id) asc
for update
skip locked

ID | VAL        |           Q | STATUS
-: | :--------- | ----------: | :-----
20 | 8XS78B92M2 |  1263442844 | O     
40 | XLLUN9DLV4 |  -435709224 | O     
60 | 2DZ4EUH11J | -1328077826 | O     
80 | PJKQMOBISR |  -644473876 | O     

select *
from table(
  dbms_xplan.display_cursor(
  format => 'BASIC +PREDICATE +ALLSTATS LAST'
  ))

| PLAN_TABLE_OUTPUT                                                                                    |
| :--------------------------------------------------------------------------------------------------- |
| EXPLAINED SQL STATEMENT:                                                                             |
| ------------------------                                                                             |
| select /*+gather_plan_statistics*/ * from t where   decode(status, 'O',                              |
| id) is not null   and rownum < 5 order by decode(status, 'O', id) asc                                |
| for update skip locked                                                                               |
|                                                                                                      |
| Plan hash value: 2984481354                                                                          |
|                                                                                                      |
| ---------------------------------------------------------------------------------------------------- |
| | Id  | Operation                     | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | |
| ---------------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT              |          |      1 |        |      4 |00:00:00.01 |       8 | |
| |   1 |  FOR UPDATE                   |          |      1 |        |      4 |00:00:00.01 |       8 | |
| |*  2 |   COUNT STOPKEY               |          |      1 |        |      4 |00:00:00.01 |       2 | |
| |   3 |    TABLE ACCESS BY INDEX ROWID| T        |      1 |      4 |      4 |00:00:00.01 |       2 | |
| |*  4 |     INDEX FULL SCAN           | IX_SFU_T |      1 |        |      4 |00:00:00.01 |       1 | |
| ---------------------------------------------------------------------------------------------------- |
|                                                                                                      |
| Predicate Information (identified by operation id):                                                  |
| ---------------------------------------------------                                                  |
|                                                                                                      |
|    2 - filter(ROWNUM<5)                                                                              |
|    4 - filter("T"."SYS_NC00005$" IS NOT NULL)                                                        |
|                                                                                                      |

/*To show which rows were locked*/
create table t_log(
  s_id int,
  id int
)

create procedure l(s int)
as
  pragma autonomous_transaction;
  type t_tab is table of t%rowtype
    index by pls_integer;
  t_data t_tab;
  i pls_integer;
  
  /*Lock will be applied
  to result set only*/
  cursor c is
  select *
  from t
  where
    decode(status, 'O', id) is not null
  order by decode(status, 'O', id) asc
  for update
  skip locked;

begin
  
  open c;
  fetch c
    bulk collect into t_data
    /*Will fetch 5 not locked rows*/
    limit 5
  ;
  
  /*Step into recursion
  to demonstrate concurrent locking*/
  if s > 0 then
    l(s-1);
  end if;
  
  forall i in 1..t_data.count
    insert into t_log(s_id, id)
      values(s, t_data(i).id);
  commit;
  close c;
end;
/

begin
  l(3);
end;
/

/*Observe results*/
select *
from t_log;

S_ID | ID
---: | --:
0 | 320
0 | 340
0 | 360
0 | 380
0 | 400
1 | 220
1 | 240
1 | 260
1 | 280
1 | 300
2 | 120
2 | 140
2 | 160
2 | 180
2 | 200
3 | 20
3 | 40
3 | 60
3 | 80
3 | 100

---

Related Topics