Very tough Challenge.....
-
How can u get top 2 rows, middle 2 rows, and last 2 rows from table but without using top keyword. No metter table is assending or dessinding....
Plz reply as early as possible. It's URGENT !!!
Thanx in Advance....
-
We need the table definition, sample data, the required output from that sample data. We also need to know what you define as the middle rows.
And knowing for which cool you need this homework would be pretty nice too.
-
OK...
i hv a table tblDesidnation which hv 2 fields.
DesignationName varchar(100), Description Varchar(1000)
if table hv 50 row then the desire output is first 2 rows, middle 2 rows means record no 25 and 26 (no metter wht is the data) and last 2 record....
Plz Help...
-
DECLARE @Challenge TABLE (
RowNo INT IDENTITY
DesignationName varchar(100)
Description Varchar(1000)
)
DECLARE @LastRow INT, @MiddleRow INT
INSERT INTO @Challenge (DesidnationName, Description)
SELECT * FROM tblDesignation
ORDER BY DesidnationName --(replace with order of your choice here)
SELECT @LastRow = @@RowCount
SELECT @MiddleRow = @LastRow/2
SELECT * FROM @Challenge WHERE RowNo in (1,2,@MiddleRow, @MiddleRow +1, @LastRow-1, @LastRow)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
In a procedure :
CREATE TABLE #tmp (ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, DesignationName VARCHAR(100), Description VARCHAR(1000))
INSERT INTO #tmp (DesignationName, Description)
SELECT DesignationName, Description FROM dbo.YourBaseTable ORDER BY ?
--the order by in this select must be the same of the clustered index, you can also use an index hint to make sure you preserve the data order.
DECLARE @top AS INT
SELECT @Top = MAX(ID) FROM #tmp
SELECT 'TOP 2' AS WhatRows, DesignationName, Description FROM #tmp WHERE ID < 3
UNION ALL
SELECT MIDDLE 2' AS WhatRows, DesignationName, Description FROM #tmp WHERE ID IN (@Top / 2, @Top / 2 + 1)
UNION ALL
SELECT 'LAST 2' AS WhatRows, DesignationName, Description FROM #tmp WHERE ID >= @Top - 1
DROP TABLE #tmp
Of course you would also need to take into consideration what would happen if you have less than 6 rows in the table, because in that case my current query would return duplicates. You also need to check out the IN (@Top / 2, @Top / 2 + 1) behavior because the rounding may not be too obvious, so you want to make sure you are getting exactly what you want in there.
-
Ya even better, use a single in statement. Learn something new every day
. -
Not sure, Gail, but I think you just did someone's "urgent" homework for them...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hence my more complicated / advanced t-sql solution
. -
Maybe. Won't be the first time.
I used to do student marking when I was at university. The markers had a habit of doing an internet search for code if it didn't match the student's normal work. Caught a few out doing that.
btw, why did you edit away your long list of points and notes. Was very valid.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thank you sir...It is my homework for today..
thanx again
-
Don't post your homework here like that again. Homework is supposed to be for you to do to learn something, not for other people to do for you.
You learn nothing by copying code. In addition, you're presenting some one else's work as if it was your own. That's plagarism and most educational institutes take a very dim view of that.
If you need help with homework, try it yourself and if you run into problems, ask, stating that it's homework. You'll probably find that someone is willing to point you in the right direction.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
He y jeff,can you repost your list of valid points, I'd like to read them now that our homework is over!
-
Figured the original poster wouldn't have gotten a thing out of it... you know... the "flying pig" thing...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, explain the code Gail wrote for you... I wanna make sure you actually learned something other than how to get other people to do your work for you...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
So, explain the code Gail wrote for you... I wanna make sure you actually learned something other than how to get other people to do your work for you... --Jeff Moden Yeah, then you post your explanation and we'll mark it
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply