select query with custom sorting

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

General SQL Server Forums

New to SQL Server Programming

select query with custom sorting

Author

Topic

amuluri
Starting Member

8 Posts

Posted - 2009-02-20 : 05:58:21

Hi,

I am a Classic ASP developer working for a online education providing company.

I have a problem with the sorting for the rows with select query.

My table looks like the following
-------------------------
QuesNo---TopicId
-------------------------
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5
--------------------------
I need to the out put like the following table

-------------------------
QuesNo---TopicId
-------------------------
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
3 1
3 3
4 3
--------------------------

Can any one please help me in solving this issue.

Thanks in advance...

matty
Posting Yak Master

161 Posts

Posted - 2009-02-20 : 06:01:16

ORDER BY QuesNo,TopicId

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:01:38

select * from <yourtable> order by 1,2

amuluri
Starting Member

8 Posts

Posted - 2009-02-20 : 06:33:35

Thanks for you replies .

But the output is coming in ascending order.
I need like
Quesno - TopicId
---1-------1---
---1-------2---
---1-------3---
---2-------1---
---2-------2---
---3-------1---

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:45:23

quote:
Originally posted by amuluri

Thanks for you replies .

But the output is coming in ascending order.
I need like
Quesno - TopicId
---1-------1---
---1-------2---
---1-------3---
---2-------1---
---2-------2---
---3-------1---

Your output is sorted in ascending manner only.

amuluri
Starting Member

8 Posts

Posted - 2009-02-20 : 06:53:05

Thanks for you reply

It is in ascending order with topicid looping 1 to 3 for quesno 1 and 1 to 2 for quesno 2 and so on.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 06:56:55

quote:
Originally posted by amuluri

Thanks for you reply

It is in ascending order with topicid looping 1 to 3 for quesno 1 and 1 to 2 for quesno 2 and so on.

Sorry, Ignore my previous post.
Anyhow, The query should return you the same. Can you post what query you used, what output it gave ?? Just wanted to see how it differs from your expected output.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 07:00:36

check this
declare @tabl table(QuesNo int,TopicId int)
insert into @tabl select
1, 1 union all select
2 ,1 union all select
3, 1 union all select
1, 2 union all select
2, 2 union all select
1, 3 union all select
2, 3 union all select
3, 3 union all select
4, 3 union all select
1, 4 union all select
2, 4 union all select
1, 5

--select * from @tabl order by quesno,topicid
select * from @tabl order by 1,2
what sakets is suggested

amuluri
Starting Member

8 Posts

Posted - 2009-02-20 : 07:13:14

Hi sakets,

the query I used is "SELECT qno,topicid FROM quant_questions order by quesno,topicid"

and it gave me the output

QuesNo---TopicId
-------------------------
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 07:17:11

quote:
Originally posted by amuluri

Hi sakets,

the query I used is "SELECT qno,topicid FROM quant_questions order by ~~quesno~~qno,topicid"

and it gave me the output

QuesNo---TopicId
-------------------------
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-20 : 07:17:47

[code]
hi amuluri,
declare @tabl table(QuesNo int,TopicId int)
insert into @tabl select
1, 1 union all select
2 ,1 union all select
3, 1 union all select
1, 2 union all select
2, 2 union all select
1, 3 union all select
2, 3 union all select
3, 3 union all select
4, 3 union all select
1, 4 union all select
2, 4 union all select
1, 5

SELECT QuesNo,topicid FROM @tabl order by quesno,topicid
giving o/p as
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
3 1
3 3
4 3
[/code]

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-20 : 07:17:47

quote:
Originally posted by sodeep

quote:
Originally posted by amuluri

Hi sakets,

the query I used is "SELECT qno,topicid FROM quant_questions order by ~~quesno~~qno,topicid"

and it gave me the output

QuesNo---TopicId
-------------------------
1 1
2 1
3 1
1 2
2 2
1 3
2 3
3 3
4 3
1 4
2 4
1 5

Or you must be kidding.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-20 : 07:22:34

definitely kidding.If you run select qno,topicid ,, how can your output columns be named QuesNo---TopicId ?????

amuluri
Starting Member

8 Posts

Posted - 2009-02-20 : 07:40:41

Hi sakets , sodeep ,bklr and matty

Actually i had another level called subtopic which was also in my select list and couldnot recognize the output . But finally it is working.

the query Iam using is "
SELECT MAX(revisionlevel) as revisionlevel,qno,subtopicid,topicid FROM
mygmatstudy_user.cat_quant_question GROUP BY qno,topicid,subtopicid
order by qno,subtopicid,topicid"

And I am very much thank full to all of you helped in solving this issue. With out this query I must write few 10 s of lines of code which this query gave me in one shot.

Thank You All..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources