My query is... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

My query is...

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-14 : 03:00:54

I have a table
mytable(col1 int) with 10 records 1 - 10 in jumbling
say..
9
7
8
6
5
3
4
1
2
10 like this
My output should have 2 columns with one column should display in ascending another should display in descending.
ie., Output should look like this

col1 col2
----------
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2
10 1
----------

can any one tell me how to do this..
This is one interview question..

Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:12:03

SELECT Col1, 11 - Col1 AS Col2
FROM MyTable
ORDER BY Col1

E 12°55'05.25"
N 56°04'39.16"

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-14 : 03:24:19

Thank you very much..
If cols value varies means how to do, now we have 1-10,no plm, but if our values in the columns are
12,23,10,35,100 ... like this, now how to do,..
Ganesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-14 : 03:40:29

Why do you ask? Did the interviewer ask you two questions?
Have a look at ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 03:48:57

[code];
With CTE(Seq,RSeq,Col) AS
(SELECT ROW_NUMBER() OVER (ORDER BY col1) AS Seq,
ROW_NUMBER() OVER (ORDER BY col1 DESC) AS RSeq,
col1
FROM Table)

select c1.Col,c2.Col
FROM CTE c1
INNER JOIN CTE c2
ON c1.Seq=c2.RSeq[/code]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-05-14 : 04:40:13

Thank you..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources