Database

Ranojay wrote:

How do i create a "VIEW" in SQL in MS-ACCESS Databases ?

Help!

select top 1 itemnumber into #a from stocktable 
order by itemnumber

select top 3 itemnumber into #b from stocktable
order by itemnumber

select #b.itemnumber from #b
left join #a
on #b.itemnumber = #a.itemnumber
where #a.itemnumber is null

Create Table #r
(
id --Identity column. Use your SQL databas's syntax here.
--For example, SQL Server is Identity(1,1).
...--Columns in the table
)

Insert #r(...--Columns in table)
Select *
From YourTable
Order By YourColumn

Select *
From #r
Where id in (2,3)

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ; 
INSERT INTO #T (aField) SELECT 'one' ; 
INSERT INTO #T (aField) SELECT 'two' ; 
INSERT INTO #T (aField) SELECT 'three' ; 
INSERT INTO #T (aField) SELECT 'four' ; 
INSERT INTO #T (aField) SELECT 'five' ; 
INSERT INTO #T (aField) SELECT 'six' ; 
INSERT INTO #T (aField) SELECT 'seven' ; 

/* SORTED LIST */
/****************************/
SELECT * FROM #T ORDER BY aField DESC 

/* EXTRACT FROM SORTED LIST */
/****************************/
;WITH myTable AS (
    SELECT 
          ROW_NUMBER() OVER( ORDER BY aField DESC ) AS RowNo
        , id
        , aField
    FROM #T
    )
SELECT * 
FROM myTable 
WHERE RowNo IN ( 2 , 4 ) ; 
/****************************/

DROP TABLE #T ; 

/* TEST DATA */
/****************************/
CREATE TABLE #T (id INT IDENTITY(1,1), aField NVARCHAR(10) ) ; 
INSERT INTO #T (aField) SELECT 'one' ; 
INSERT INTO #T (aField) SELECT 'two' ; 
INSERT INTO #T (aField) SELECT 'three' ; 
INSERT INTO #T (aField) SELECT 'four' ; 
INSERT INTO #T (aField) SELECT 'five' ; 
INSERT INTO #T (aField) SELECT 'six' ; 
INSERT INTO #T (aField) SELECT 'seven' ; 

/* TEST LIST */
/****************************/
SELECT IDENTITY(INT, 1 , 1) AS RowNo, 0+id AS id, aField 
INTO #Z 
FROM #T 
ORDER BY aField DESC 

/* SORTED LIST */
/****************************/
SELECT * FROM #Z

/* EXTRACT FROM SORTED LIST */
/****************************/
SELECT * FROM #Z WHERE RowNo IN ( 2 , 4 ) ;
/* */
DROP TABLE #T ; 
DROP TABLE #Z ; 

David Mujica wrote:

If you really only have 20 or so records

David Mujica wrote:

save yourself lots of headaches with complex SQL