Showing posts with label suppose. Show all posts
Showing posts with label suppose. Show all posts

Monday, March 12, 2012

Query Paging

let's suppose that we have a table entitled "tab1" which has more than 1000 rows and about 10 columns

so in SQL 2000, if I do this query:

SELECT * FROM tab1

the result will be displaying all the rows from the begining.

and my teacher told me that there's a new option in SQL 2005 which is you can display the result of the query in a page mode.

so can anyone tell me how can I do so for this query:

SELECT * FROM tab1

so that I can see the results in pages.

thanks

This is probably related to T-SQL.|||

I guess you mean the RANK option where you can number the result set to use paging, more information about that can be found in the BOL.

HTH, Jens Suessmeyer.


http://www.sqlserver2005.de

|||? Some information on my blog here: http://sqljunkies.com/WebLog/amachanic/archive/2004/11/03/4945.aspx Let me know if you have any questions. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Uncle Sam@.discussions.microsoft.com> wrote in message news:3653b2ba-3ba6-4cca-a4f7-14ce53222af2@.discussions.microsoft.com... let's suppose that we have a table entitled "tab1" which has more than 1000 rows and about 10 columns so in SQL 2000, if I do this query: SELECT * FROM tab1 the result will be displaying all the rows from the begining. and my teacher told me that there's a new option in SQL 2005 which is you can display the result of the query in a page mode. so can anyone tell me how can I do so for this query: SELECT * FROM tab1 so that I can see the results in pages. thanks|||

sorry, it is not what I need,

my question is simple. I don't want to write any procedures or something else, I just want to see the result of "SELECT * FROM tab1" in a grid or text with pages.

Just like any word program, so I can click on next to go to the next page or something like that.

My teacher told me that it is a new feature in SQL 2005 and I have searched for it and still not found it.

thanks

|||Hi,

I guess you don′t mean then the new functionality of SQL Server rather than the one from ASP.NET 2.0 and the new datalistcontrol:

http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

no man its not what I want, what I want is a new service or a procedure tha makes the results of queries appear in pages.

I am sure that It exists but please I want some information about it, It's urgent.

Thanks

Wednesday, March 7, 2012

query on recursive table

Hi,
Suppose I'm working on a database containing 2 tables:
tStudent (student information)
========
|--|
| studentID | studentName | deptID |
|--|
| 504001 | John Doe | 10001 |
| 504005 | JI Jane | 200 |
|--|
tItems (organisational information)
======
|----|
| itemID | KindOfItem | itemName | parentID |
|----|
| 1 | top | top | <NULL> |
| 10 | school | Univ of Geel | 1 |
| 11 | school | Univ of Antwerp | 1 |
| 12 | school | Univ of Brussels | 1 |
| 100 | dept | science dept | 10 |
| 101 | dept | human science dept | 10 |
| 200 | dept | agriculture dept | 11 |
| 1001 | dept | computer science dept | 100 |
| 10001 | dept | Windows comp science dept | 1001 |
| 10002 | dept | Unix comp science dept | 1001 |
|----|
I need to write a query using Reporting Services on a MS SQL server that
gives me for every student the dept he is in (that's the easy part), but
also the top level school the department resides under. Result should be
like:
504001, John Doe , Windows comp science dept, Univ of Geel
504005, JI Jane, Agriculture dept, Univ of Antwerp
Can anybody give me any clues on how to do this (if possible)?
ErikWhy not add the parent_id for say Univ of Geel is 1 to a new column in the
first table and use an update to achieve this. Then you all you need is a
simple equi-join.
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||You didn't specify if the dept of the recursion is variable. Assuming
it is not then:
select x.studentID, x.studentName, y.DeptName, y.School
from tStudent x,
(
select a.deptID, a.itemName as DeptName, b.itemName as School
from tItems a, tItems b
where a.parentID = b.deptID
) y
where x.deptID = y.deptID
Ranny

> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
User submitted from AEWNET (http://www.aewnet.com/)|||This smells more like homework instead of real world scenario, but I'll play
along! how about this:
set nocount on
declare @.organization table (itemid int, kindofitem varchar(20), itemname
varchar(30), parentid int, topparentid int)
declare @.studentinfo table (studentid int, studentname varchar(30), deptid
int)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10, 'school', 'Univ of Geel', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(11, 'school', 'Univ of Antwerp', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(12, 'school', 'Univ of Brussels', 1)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(100, 'dept', 'science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(101, 'dept', 'human science dept', 10)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(200, 'dept', 'agriculture dept', 11)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(1001, 'dept', 'computer science dept', 100)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10001, 'dept', 'Windows comp science dept', 1001)
insert into @.organization (itemid, kindofitem, itemname, parentid)
values(10002, 'dept', 'Unix comp science dept', 1001)
insert into @.studentinfo (studentid, studentname, deptid) values(504001,
'John Doe', 10001)
insert into @.studentinfo (studentid, studentname, deptid) values(504005, 'JI
Doe', 200)
update @.organization set topparentid = itemid where parentid = 1
while 1 = 1
begin
update @.organization
set topparentid = k.topparentid
from @.organization o
join (select itemid, topparentid from @.organization where topparentid is
not null) k
on o.parentid = k.itemid
where o.topparentid is null
if @.@.rowcount = 0
break
end
select * from @.organization
"dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
> Hi,
> Suppose I'm working on a database containing 2 tables:
> tStudent (student information)
> ========
> |--|
> | studentID | studentName | deptID |
> |--|
> | 504001 | John Doe | 10001 |
> | 504005 | JI Jane | 200 |
> |--|
> tItems (organisational information)
> ======
> |----|
> | itemID | KindOfItem | itemName | parentID |
> |----|
> | 1 | top | top | <NULL> |
> | 10 | school | Univ of Geel | 1 |
> | 11 | school | Univ of Antwerp | 1 |
> | 12 | school | Univ of Brussels | 1 |
> | 100 | dept | science dept | 10 |
> | 101 | dept | human science dept | 10 |
> | 200 | dept | agriculture dept | 11 |
> | 1001 | dept | computer science dept | 100 |
> | 10001 | dept | Windows comp science dept | 1001 |
> | 10002 | dept | Unix comp science dept | 1001 |
> |----|
> I need to write a query using Reporting Services on a MS SQL server that
> gives me for every student the dept he is in (that's the easy part), but
> also the top level school the department resides under. Result should be
> like:
> 504001, John Doe , Windows comp science dept, Univ of Geel
> 504005, JI Jane, Agriculture dept, Univ of Antwerp
> Can anybody give me any clues on how to do this (if possible)?
> Erik
>|||@.aew_nospam.com schreef:
> You didn't specify if the dept of the recursion is variable. Assuming
> it is not then:
It IS variable, as you can see from the example:
Student JI Jane resides under dept "agriculture dept", that resides
directly under school "Univ of Antwerp".
Student John Doe resides under dept "Windows comp sc dept", that resides
under dept "computer science dept" that resides under dept "science
dept" that resides under school "Univ of Geel".

> select x.studentID, x.studentName, y.DeptName, y.School
> from tStudent x,
> (
> select a.deptID, a.itemName as DeptName, b.itemName as School
> from tItems a, tItems b
> where a.parentID = b.deptID
> ) y
> where x.deptID = y.deptID
> Ranny
>
>
>
> User submitted from AEWNET (http://www.aewnet.com/)|||
> This smells more like homework instead of real world scenario, but I'll pl
ay
> along! how about this:
Please have your nose checked, because this is no homework but
real-world scenario :) It is part of a student administration system
we're implementing in a group of schools.
My job is to create report(s) from the database. The report mentionned
in OP should have the data on the student, the dept he works in and the
school this dept belongs to. Hope it's clear now :)
> set nocount on
> declare @.organization table (itemid int, kindofitem varchar(20), itemname
> varchar(30), parentid int, topparentid int)
> declare @.studentinfo table (studentid int, studentname varchar(30), deptid
> int)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10, 'school', 'Univ of Geel', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(11, 'school', 'Univ of Antwerp', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(12, 'school', 'Univ of Brussels', 1)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(100, 'dept', 'science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(101, 'dept', 'human science dept', 10)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(200, 'dept', 'agriculture dept', 11)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(1001, 'dept', 'computer science dept', 100)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10001, 'dept', 'Windows comp science dept', 1001)
> insert into @.organization (itemid, kindofitem, itemname, parentid)
> values(10002, 'dept', 'Unix comp science dept', 1001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504001,
> 'John Doe', 10001)
> insert into @.studentinfo (studentid, studentname, deptid) values(504005, '
JI
> Doe', 200)
> update @.organization set topparentid = itemid where parentid = 1
> while 1 = 1
> begin
> update @.organization
> set topparentid = k.topparentid
> from @.organization o
> join (select itemid, topparentid from @.organization where topparentid i
s
> not null) k
> on o.parentid = k.itemid
> where o.topparentid is null
> if @.@.rowcount = 0
> break
> end
> select * from @.organization
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||marcmc schreef:
> Why not add the parent_id for say Univ of Geel is 1 to a new column in the
> first table and use an update to achieve this. Then you all you need is a
> simple equi-join.
Yes, that is true. But the information I need is available already in
the database, so adding the new column in the first table would make the
information redundant...
>
> "dot" <""Erik(dot)Thijs"@.removethis.khk(" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Sample data is also a good idea, along with clear
specifications.
Have you thought about designing a normalized schema which does not mix
data and metadata? This is a better approach in the long run than
looking for kludges. Just from this posting and the silly 'T-"
prefixes that you are an OO programmer no training in SQL. OO models do
not work with SQL.