If we don't have any identity column and yet, we wish to see on what position does record lies, here is simple Query I did.
We are using ROW_NUMBER ( ) Without using ORDER BY clause. That means, We are suppressing the effect of ORDER BY Clause used in ROW_NUMBER ( )
CREATE TABLE C (NAME VARCHAR(100))
INSERT INTO C
SELECT ('ABC') UNION ALL
SELECT ('bharath') UNION ALL
SELECT ('job') UNION ALL
SELECT ('raghavendra') UNION ALL
SELECT ('anita') UNION ALL
SELECT ('prakash') UNION ALL
SELECT ('dhinakaran')
SELECT * FROM
C
We know name 'Raghavendra' lies at 4th Position and if we want to get this position number, then, we think of ROW_NUMBER ( ) concept but we know ROW_NUMBER ( ) will order the sequence the Records are stored and then It displays it.
So in order to avoid this ordering of the records, I will use some dummy values as shown below in query.
SELECT * FROM
(SELECT *, ROW_NUMBER () OVER (PARTITION BY (SELECT (0)) ORDER BY (SELECT (0))) NUMBER FROM C)A
Running this query will give the position of all the records without sorting.
Here SELECT (0) has No value and acts as a Dummy Value to generate Numbers without Ordering records.
Thus we can give Where clause to know Position of 'Raghavendra'.
SELECT * FROM (SELECT *, ROW_NUMBER ( ) OVER (PARTITION BY (SELECT (0)) ORDER BY (SELECT (0))) NUMBER FROM C)A
WHERE NAME='RAGHAVENDRA'
Any better Solution are welcomed.
Comments
Post a Comment