February 11, 2011 at 7:52 am
I have table like this
col1 col2 col3
5 3 8
6 2 9
7 1 10
and i want result as
col1 col2 col3
5 1 8
6 2 9
7 3 10
ie, col2 also in ascending order.
What is the query..?
Order by col1 asc,col2 asc, col3 asc is not working..please check
February 11, 2011 at 8:50 am
What are the data types of these columns? Also, could you be more specific than "it isn't working"?
Jim
February 11, 2011 at 9:24 am
your example's messing you up... a row of data is just that: each element is related to the others. grabbing a value from another row? why?
if you substitute names for your numbers, you'll see why it doesn't seem right:
col1 col2 col3
George Washington Virginia --5 3 8
Abraham Lincoln Illinois --6 2 9
Bill Clinton Arkansas -- 7 1 10
would you really want:
col1 col2 col3
George Clinton Virginia --5 1 8
Abraham Lincoln Illinois --6 2 9
Bill Washington Arkansas --7 3 10
show us the REAL code(not the pseudo code you tried to show as an example) so we can really help you.
Lowell
February 14, 2011 at 10:46 pm
SELECT
tCOL1COL3.COL1 AS COL1,
tCOL2.COL2 AS COL2,
tCOL1COL3.COL3 AS COL3
FROM
(
SELECT
COL1,
COL3,
RANK () OVER(ORDER BY COL1 ASC) AS ROWNUMBER
FROM
SQLSerC /* YOUR TABLE HAVING COLUMNS COL1 (INT), COL2(INT), COL3(INT) */
) tCOL1COL3
INNER JOIN
(
SELECT
COL2,
RANK () OVER(ORDER BY COL2) AS ROWNUMBER
FROM
SQLSerC /* YOUR TABLE HAVING COLUMNS COL1 (INT), COL2(INT), COL3(INT) */
) tCOL2
ON
tCOL1COL3.ROWNUMBER = tCOL2.ROWNUMBER
Sample Run Results ...
RunStatus COL1 COL2 COL3
Before Run ... 5 3 8
Before Run ... 6 2 9
Before Run ... 7 1 10
RunStatus COL1 COL2 COL3
After Run ... 5 1 8
After Run ... 6 2 9
After Run ... 7 3 10
February 16, 2011 at 1:45 am
Thank you Amartha dutta
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply