Fetch data from table using SQL

Question

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?

In that case, all four answers below are correct. Just pick whatever it easiest for you to maintain (if your data were huge, you might want to look at performance differences of BETWEEN vs >= AND — Evan Volgas, Apr 12 '14 at 16:37
@evanv: `BETWEEN` has no difference in performance vs. using `>=` and `<=` — ypercubeᵀᴹ, Apr 12 '14 at 18:08

potashin · Accepted Answer · 2015-01-28T02:53:34.913

3

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)

edited Jan 28 '15 at 02:53

answered Apr 12 '14 at 16:26

potashin

43,297
11
81
105

Can it be done using BETWEEN. Will it fetch 50 and 1000 inclusive? – Assassin Apr 12 '14 at 16:30

Milica Medic Kiralj · Answer 2 · 2014-04-12T16:40:22.510

2

You can try something like this:

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

Hope this helps

edited Apr 12 '14 at 16:40

answered Apr 12 '14 at 16:30

Milica Medic Kiralj

3,540
28
30

Will this fetch rows 50 and 1000 inclusive or just values between them? – Assassin Apr 12 '14 at 16:33
1

It will get 50 and 1000 also. See: http://technet.microsoft.com/en-us/library/ms187922.aspx – Milica Medic Kiralj Apr 12 '14 at 16:33
1

thanks..i think BETWEEN is the way to go if performance is considered. I wish i could accept your answer as well but @Notulysses marked the beginning with the correct answer. – Assassin Apr 12 '14 at 17:08

score 2 · Answer 3 · answered Apr 12 '14 at 16:32

2

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.

answered Apr 12 '14 at 16:32

Brian DeMilia

12,688
1
20
31

Thanks for the reply!! – Assassin Apr 12 '14 at 17:08

score 0 · Answer 4 · edited May 23 '17 at 11:57

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

Milica's answer is also good, if your so_no is easily filterable like that (or if you have an auto increment column that accurately orders your order numbers) — Evan Volgas, Apr 12 '14 at 16:34

Fetch data from table using SQL

4 Answers4