CODEWITHSUNDEEP
sqlsql-serverdatabase
Assassin
Assassin

Reputation: 215

Fetch data from table using SQL

I have a table named "Orders" with 1-1000 rows and 3 columns (S.no, Order and Status). I need to fetch Order from 50-1000 which has its Status as "Cancelled". How can i do this in SQL Server?

Upvotes: 2

Views: 143

Answers (4)

potashin
potashin

Reputation: 44611

Logic operator:

SELECT Order 
FROM Orders 
WHERE Status = 'Cancelled'
  AND (S.no > 50 AND S.no < 1000)

BETWEEN:

SELECT Order 
FROM Orders 
WHERE Status = 'Cancelled'
  AND (S.no BETWEEN 50 and 1000)

Upvotes: 3

Milica Medic Kiralj
Milica Medic Kiralj

Reputation: 3780

You can try something like this:

SELECT *
FROM Orders
WHERE (S.no BETWEEN 50 AND 1000) AND (Status = 'Cancelled')

Hope this helps

Upvotes: 2

Brian DeMilia
Brian DeMilia

Reputation: 13248

select *
  from orders
 where no between 50 and 1000
   and status = 'Cancelled'

Assuming you meant to say that the column was named "no". S.no would not be a valid column name.

Upvotes: 2

Evan Volgas
Evan Volgas

Reputation: 2921

If you're using SQL Server, you don't have access to Limit and Offset (unless that's changed in the last year or so, in which case please someone correct me).

There's a really nice generalizable solution discussed here: Equivalent of LIMIT and OFFSET for SQL Server?

I'd definitely take a look at that. If indeed your s_no values range from 1-1000, then the solution above by Notulysses should work just fine. But if you don't have so_no between 1-1000 (or in some other easy to filter way) then check out the solution linked to above. If you can what Notulysses recommended, go for it. If you need a generalizable solution, the one above is very good. I've also copied it below, for reference 

  ;WITH Results_CTE AS
(
    SELECT
        Col1, Col2, ...,
        ROW_NUMBER() OVER (ORDER BY SortCol1, SortCol2, ...) AS RowNum
    FROM Table
    WHERE <whatever>
)
SELECT *
FROM Results_CTE
WHERE RowNum >= @Offset
AND RowNum < @Offset + @Limit

Upvotes: 0

Related Questions