Row_Number and Union query

Question

Row_Number and Union query

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 1

[font="Verdana"]

...and I had programatically implemented paging in asp.net by looping through all records returned by the query...

If you have Asp. Net as fron end, then what cause you to do the looping the records? Asp. Net has Datagrid control where you could have done pagging easily. You need to just bind the data to Datagrid, no need to loop more.

Mahesh

[/font]

MH-09-AM-8694

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 2

And if the query returns a million rows, then what?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ahsansharjeel SSC Enthusiast Points: 149 More actions · Answer 3

Your Solution is here, Please change the SELECT Queries with your Query

SELECT * , ROW_NUMBER() OVER(ORDER BY ID) AS MY_ROW_NUM FROM

(

SELECT ID, FirstName, LastName, JoinDate FROM Table1

UNION

SELECT ID, FirstName, LastName, JoinDate FROM Table2

)

SUB

Jeff Moden SSC Guru Points: 1004401 More actions · Answer 4

ahsansharjeel (5/12/2008)
Your Solution is here, Please change the SELECT Queries with your Query
SELECT * , ROW_NUMBER() OVER(ORDER BY ID) AS MY_ROW_NUM FROM
(
SELECT ID, FirstName, LastName, JoinDate FROM Table1
UNION
SELECT ID, FirstName, LastName, JoinDate FROM Table2
)
SUB

Again, I ask, what if you have a million rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wasimshahzad SSC Rookie Points: 49 More actions · Answer 5

Add simiar to this, It works

SELECT (ROW_NUMBER() OVER (Order By EMPID1 ))AS SrNo,*

( UNION Queries)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Please note: two year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

Forum ghosts resurrecting threads! Arch! :-D:-D:-D

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]