How to get data sequentially?

Question

This is an interview question.

This is a actual table Employee:

id  |  name  | salary
1   |    A   | 7000
2   |    B   | 6000
3   |    C   | 5000
4   |    D   | 5500
5   |    E   | 4000
6   |    F   | 4800
7   |    G   | 3000
8   |    H   | 2000

And I want a result like this:

id  |  name  | salary
1   |    C   | 5000
2   |    D   | 5500
3   |    E   | 4000
4   |    F   | 4800
5   |    G   | 3000

I want to show records whose salary between 5000 to 3000 but 1st column should be sequential.

When I perform query it shows this result.

id  |  name  | salary
3   |    C   | 5000
4   |    D   | 5500
5   |    E   | 4000
6   |    F   | 4800
7   |    G   | 3000

[here] what can I write then it will give proper result?

 select name, salary, [HERE]
 from Employee
 where salary between 5000 and 3000;

Seems like a duplicate of http://stackoverflow.com/questions/1293390/sql-to-output-line-number-in-results-of-a-query — gview, Nov 07 '13 at 07:10

score 1 · Accepted Answer · answered Nov 07 '13 at 07:23

1

This is standard ANSI SQL ^{(supported by all modern DBMS)}

select row_number() over () as id
       name,
       salary
where salary between 5000 and 3000

Note that the order of rows (and in this case the "generated") id is undefined if you do not specify an order by clause. To get a "stable sort order", you should use something like this:

select row_number() over (order by salary) as id
       name,
       salary
where salary between 5000 and 3000
order by salary

answered Nov 07 '13 at 07:23

SELECT ROW_NUMBER() OVER (ORDER BY SomeField) AS Row, * FROM SomeTable; its not working in lampp; – Ajinkya Kasar Nov 08 '13 at 06:31
@user2963592: MySQL isnt' advanced enough. They have not kept up with modern SQL features (although that standard is already 10 years old). If you want a DBMS specific solution, you should tag your question accordingly. At least mention it in the question – Nov 08 '13 at 07:08
Then what can I do for MySQL. is there any option.? – Ajinkya Kasar Nov 08 '13 at 08:26
@user2963592: I don't think so (apart from Sashi's answer) – Nov 08 '13 at 08:42

score 0 · Answer 2 · answered Nov 07 '13 at 07:15

0

Try this::

For MYSQL ::

SELECT @rownum:=@rownum + 1 as row_number, 
       t.*
FROM ( 
select name, salary from Employee
 where salary between 5000 and 3000) t,
(SELECT @rownum := 0) r

answered Nov 07 '13 at 07:15

Sashi Kant

13,277
9
44
71

good its working but when user want only one column then it fail. its depend on next column which is specify in SELECT clause. – Ajinkya Kasar Nov 08 '13 at 06:46
@AjinkyaKasar: didn't get you? – Sashi Kant Nov 08 '13 at 09:34

score 0 · Answer 3 · edited May 23 '17 at 12:29

0

As already commented, for MS SQL server the solution can be found in this post: https://stackoverflow.com/a/1293399/2822268

edited May 23 '17 at 12:29

Community

1
1

answered Nov 07 '13 at 07:16

Cosima

31
2

Link only answers are usually frowned upon. If you really think it's the same question, you should vote to close this as duplicate. – Nov 07 '13 at 07:27
Thanks for this tipp. Unfortunately I am currently not able to vote for closing as a duplicate as I am very new on this platform. I thought the solution for the other question could help here - the question itself is not exactly a duplicate. – Cosima Nov 07 '13 at 08:09

How to get data sequentially?

3 Answers3