June 7, 2009 at 6:34 pm
Jeff Moden (6/6/2009)
Amazing... what the hell did the boys in Redmond do? The code posted above by arijit works just dandy in 2k5. The error appears when you try it in 2k and it appears just like what arijit shows.
They might wanted to push you out of correlated subqueries. 😉
But what about this?
SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
, CASE WHEN [UnitsInStock] = 0 THEN 'Unavailable'
WHEN [UnitsInStock] <= 10 THEN 'Short'
WHEN [UnitsInStock] <= 100 THEN 'Available'
ELSE 'Overstocked'
END AS Availability
FROM [Northwind].[dbo].[Products]
ORDER BY Availability
GO
DECLARE @OrderingDesc bit
SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
, CASE WHEN [UnitsInStock] = 0 THEN 'Unavailable'
WHEN [UnitsInStock] <= 10 THEN 'Short'
WHEN [UnitsInStock] <= 100 THEN 'Available'
ELSE 'Overstocked'
END AS Availability
FROM [Northwind].[dbo].[Products]
ORDER BY CASE WHEN @OrderingDesc = 1 THEN 1 ELSE Availability END
GO
_____________
Code for TallyGenerator
June 7, 2009 at 9:11 pm
Sergiy (6/7/2009)
Jeff Moden (6/6/2009)
Amazing... what the hell did the boys in Redmond do? The code posted above by arijit works just dandy in 2k5. The error appears when you try it in 2k and it appears just like what arijit shows.They might wanted to push you out of correlated subqueries. 😉
Seems just the opposite is true because it doesn't work in 2k but it does in 2k5. 🙂
But what about this?
SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
, CASE WHEN [UnitsInStock] = 0 THEN 'Unavailable'
WHEN [UnitsInStock] <= 10 THEN 'Short'
WHEN [UnitsInStock] <= 100 THEN 'Available'
ELSE 'Overstocked'
END AS Availability
FROM [Northwind].[dbo].[Products]
ORDER BY Availability
GO
DECLARE @OrderingDesc bit
SELECT [ProductID], [ProductName], [SupplierID], [CategoryID], [QuantityPerUnit], [UnitPrice], [UnitsInStock], [UnitsOnOrder], [ReorderLevel], [Discontinued]
, CASE WHEN [UnitsInStock] = 0 THEN 'Unavailable'
WHEN [UnitsInStock] <= 10 THEN 'Short'
WHEN [UnitsInStock] <= 100 THEN 'Available'
ELSE 'Overstocked'
END AS Availability
FROM [Northwind].[dbo].[Products]
ORDER BY CASE WHEN @OrderingDesc = 1 THEN 1 ELSE Availability END
GO
I agree that if the first query works, so should the second (but it doesn't). I guess it's just a matter of design or not and what you're saying is it's a failure by design and there's not much we can do about it.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 7, 2009 at 9:39 pm
I am not this expert in SQL to be able to discuss further on the design issues and flaws of the SQLServer 2000, but I am happy that this forum helped me find answer to my original question, that of "finding sum of top n rows alongwith group".
Arun's solution in 3rd post helped be out of this tangle, and I am using this at in production at our college website http://www.jaipuriacollegekolkata.com/valid-appl2.asp?courseid=6 to select students by taking top 4 marks into count.
Do you find a situation where this could fail? Or this is the simplest possible way for this problem?
create table #temp
(
studentID int,
subjectID int,
subjectMark int
)
insert into #temp
select 1, 1, 92
union all
select 1, 2, 88
union all
select 1, 3, 88
union all
select 1, 4, 83
union all
select 1, 5, 83
union all
select 1, 6, 74
union all
select 2, 1, 78
union all
select 2, 2, 89
union all
select 2, 3, 65
union all
select 2, 4, 77
union all
select 2, 5, 90
union all
select 2, 6, 81
--select * from #temp
select a.studentID,sum(subjectMark)TOP_SUM
from #temp a
where a.subjectID in( select top 4 subjectID from #temp
where studentID = a.studentID
order by subjectMark desc)
group by a.studentID
June 8, 2009 at 12:58 am
Jeff Moden (6/7/2009)
Seems just the opposite is true because it doesn't work in 2k but it does in 2k5. 🙂
No, everything was correct in the statement.
They wanted it back then, in 2000, but they don't want it any more.
:hehe:
_____________
Code for TallyGenerator
June 9, 2009 at 2:53 am
Hi Try Below query .
Select studentID ,SUM(subjectMark) as TotalMarks FROM (
Select * ,ROW_NUMBER() over(partition by studentID order by subjectMark desc) as rowno
from tstudents)as Data Where Rowno < 5 Group by studentID
Check above query .
Let me Know .
if u want send Personal Mail Also.
Regards
Venkat.
June 9, 2009 at 3:04 am
Hi Try Below query .
Select studentID ,SUM(subjectMark) as TotalMarks FROM (
Select * ,ROW_NUMBER() over(partition by studentID order by subjectMark desc) as rowno
from tstudents)as Data Where Rowno < 5 Group by studentID
Check above query .
Let me Know .
if u want send Personal Mail Also.
Regards
Venkat.
June 9, 2009 at 8:18 am
This would not work in SQL 2000, 'ROW_NUMBER' is not a recognized function name.
June 9, 2009 at 11:59 am
I need on a further query. How to select TOP-N SQL statements between two periods.
Something like if I would want to select 6th to 10th student in this following table.
create table #temp
(
studentid int, studentMarks int
)
insert into #temp
select 1, 92 union all
select 2, 88 union all
select 3, 88 union all
select 4, 83 union all
select 5, 83 union all
select 6, 74 union all
select 7, 78 union all
select 8, 89 union all
select 9, 65 union all
select 10, 77 union all
select 11, 90 union all
select 12, 81
--select * from #temp
select TOP 5 studentid, studentMarks from
#temp order by studentMarks desc
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply