help me to get desired output - 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

General SQL Server Forums

New to SQL Server Programming

help me to get desired output

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-03-08 : 01:44:38

Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.

--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))

select * from question_code_level

1 html beginer what is clr? something something something something something
2 c# beginer what is clr? something something something something something
3 c# expert what is clr? something something something something something
4 c# intermedia what is clr? something something something something something
5 c# beginer what is clr? something something something something something
6 vb beginer what is clr? something something something something something
7 html expert what is clr? something something something something something
8 sqlserver intermedia what is clr? something something something something something
10 oracle beginer what is clr? something something something something something
11 java beginer what is clr? something something something something something
12 java expert what is clr? something something something something something
13 sqlserver beginer what is clr? something something something something something

--select * from question_code_level where code='c#' and level='beginer'

2 c# beginer what is clr? something something something something something
5 c# beginer what is clr? something something something something something

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 01:48:17

If you are giving WHERE condition to select only C# beginner questions, how do you expect it to bring back 1st record which is for html beginner?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 01:48:53

Do the numbering in your front end application.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 01:49:57

select row_number() over (order by qno) as new_qno, code, level, question, option1, option2, option3, option4, option5
from question_code_level where code = 'c#' and level = 'beginner'

Peter Larsson
Helsingborg, Sweden

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-03-08 : 01:56:57

Dear Peso, what is row_number() ?
i didnt get this? please explain me

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-08 : 02:04:46

row_number() is built-in function provided in SQL Server 2005. It will number the records based on certain column order. (as given by ORDER BY clause)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-03-08 : 02:08:22

i've only sqlserver2000. that is the reason i'm not getting the result. is there any equialent function in sql server2000?

and what is the wrong in this query? please see this

select * into temp1 from (select * from question_code_level where code='c#' and level='beginer')

Vinod

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-08 : 02:26:06

you need to named your derived table


select * into temp1 from (select * from question_code_level where code='c#' and level='beginer') a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources