I have a table as below:
Column1:
1
10
20
30
2
40
50
60
3
70
80
90
Output should be:
Column1 Column2:
10 1
20 1
30 1
40 2
50 2
60 2
70 3
80 3
90 3
1 Answer
your data
CREATE TABLE mytable(
Column1 INTEGER NOT NULL
);
INSERT INTO mytable(Column1) VALUES (1);
INSERT INTO mytable(Column1) VALUES (10);
INSERT INTO mytable(Column1) VALUES (20);
INSERT INTO mytable(Column1) VALUES (30);
INSERT INTO mytable(Column1) VALUES (2);
INSERT INTO mytable(Column1) VALUES (40);
INSERT INTO mytable(Column1) VALUES (50);
INSERT INTO mytable(Column1) VALUES (60);
INSERT INTO mytable(Column1) VALUES (3);
INSERT INTO mytable(Column1) VALUES (70);
INSERT INTO mytable(Column1) VALUES (80);
INSERT INTO mytable(Column1) VALUES (90);
use NTILE in sql server it acts as Tile and hence solve your issue.
SELECT *
FROM (SELECT column1,
Ntile (3)
OVER (
ORDER BY (SELECT NULL) ) Column2
FROM mytable) m
WHERE column1 <> column2
3 Comments
Himanshu
You didn't just attempt to answer this unclear question, but also answered with the best possible way.
SMor
ORDER BY (SELECT NULL) Logical nonsense. This only appears to provide a consistent result.
RF1991
Using Order by (select null) is intend to create natural numbers 1 2 3 4
2comes before40or before a different row? Remember that tables are unordered sets