Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Select 'n' records without a Table

Is there a way of selecting a specific number of rows without creating a table. e.g. if i use the following:

SELECT 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

It will give me 10 across, I want 10 New Rows.

Thanks

like image 347
James Avatar asked Jun 30 '11 10:06

James


People also ask

How do I get last 10 rows in SQL Server?

Let us now implement the above query. mysql> SELECT * FROM ( -> SELECT * FROM Last10RecordsDemo ORDER BY id DESC LIMIT 10 -> )Var1 -> -> ORDER BY id ASC; The following is the output that displays the last 10 records. We can match both records with the help of the SELECT statement.

How can I get only 10 records in MySQL?

To select first 10 elements from a database using SQL ORDER BY clause with LIMIT 10. Insert some records in the table using insert command. Display all records from the table using select statement. Here is the alternate query to select first 10 elements.

How do I select data from one table is not in another table?

We can get the records in one table that doesn't exist in another table by using NOT IN or NOT EXISTS with the subqueries including the other table in the subqueries.

How do I get the new 5 records in SQL?

METHOD 1 : Using LIMIT clause in descending order As we know that LIMIT clause gives the no. of specified rows from specifies row. We will retrieve last 5 rows in descending order using LIMIT and ORDER BY clauses and finally make the resultant rows ascending.


2 Answers

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);
like image 118
Dave Markle Avatar answered Sep 24 '22 10:09

Dave Markle


If you have a fixed number of rows, you can try:

SELECT 1
UNION
SELECT 2
UNION
SELECT 3
UNION
SELECT 4
UNION
SELECT 5
UNION
SELECT 6
UNION
SELECT 7
UNION
SELECT 8
UNION
SELECT 9
UNION
SELECT 10
like image 31
CristiC Avatar answered Sep 24 '22 10:09

CristiC