SQL query - help needed - Learner

Hi,

I have a table with the below values

labval      baso
--------      --------
1.2	test
23	test
12	test
10	test
9	test
0	test
0.1	test
2.5	test
8	test
4.9	test
3.5	test
50	test
23.2	test
3.8	test
9.2	test
8.1	test
7.2	test
7.9	test
5.1	test
30	test
3.9	test
6.1	test
8.9	test
6.9	test
2.7	test
30	Test1
20	test1
26	test1
40	test1
30.2	test1
5.9	test1
10.5	test1
13.2	test1
50	test1
33.9	test1
22	test1
0.1	test1
42	test1
0	test1
12	test1
32	test1
75	test1
80	test1
0.17	test1
17	test1
66	test1
65.2	test1
70	test1
82	test1
100	test1
20	test2
...             .... (say another different 30 values for test2)
10             test3
....            ..... (say another 70 different values for test3)


Now I need to find the maximum 5 values in each test, test1, test2,
test3 in descending order
and also the minimum 5 values in each test, test1, test2, test3 in
ascending order.

The result can look like this

val1 test
val2 test
val3 test
val4 test
val5 test

val1 test1
val2 test1
val3 test1
val4 test1
val5 test1

...   .....

val1 test3
val2 test3
val3 test3
val4 test3
val5 test3

Can some one help me with the SQL query? Do we have to use the cursors
to do this?

Thanks,

-L

SQL query - help needed - Learner

2)
t3)

I my self just tried using a cursor. Please advice.

DECLARE @BasoPhals varchar(10)
DECLARE @getBasoPhals CURSOR
SET @getBasoPhals =3D CURSOR FOR
SELECT distinct BasoPHALS FROM Test
OPEN @getBasoPhals
FETCH NEXT
FROM @getBasoPhals INTO @BasoPhals
WHILE @@FETCH_STATUS =3D 0
BEGIN
--PRINT @AccountID

SELECT     TOP (20) LabVal, BasoPHALS
FROM         Test
WHERE     BasoPHALS =3D @BasoPhals
ORDER BY LabVal, BasoPHALS desc

FETCH NEXT
FROM @getBasoPhals INTO @BasoPhals
END
CLOSE @getBasoPhals
DEALLOCATE @getBasoPhals

Where Test is the name of table. Are there any other way to do this
with using cursors?

Thanks,
-L

SQL query - help needed - Rahul

st2)
est3)

Try this,

--For Asc
Select *
From
(
Select *, Rank() Over(Partition by Val2 Order by Val1 Asc ) RowNum
From GroupTest
) Tbl
Where RowNum <=3D 5


--And for desc
Select *
From
(
Select *, Rank() Over(Partition by Val2 Order by Val1 Desc ) RowNum
From GroupTest
) Tbl
Where RowNum <=3D 5


Rahul

SQL query - help needed - Rahul

test2)
test3)

) RowNum
) RowNum


Try this,

In sql Server 2000

-------------Sql Server 2000
--For Asc
Select *, ID=3D Identity(Int, 1,1)
Into #GroupTestAsc
=46rom GroupTest

Select Val1, Val2, RowNum
From
(
Select ID, Val1, Val2,
ID - (
Select Count(Cast(Tbl2.Val2 As VarChar))
From #GroupTestAsc As Tbl2
Where (Cast(Tbl2.Val2 As VarChar)) <
(Cast(Tbl1.Val2 As VarChar))
)
As RowNum
From #GroupTestAsc As Tbl1
) Tbl
Where RowNum <=3D 5

--For Desc
Select *, ID=3D Identity(Int, 1,1)
Into #GroupTestDesc
=46rom GroupTest
Order By Val2 Asc, Val1 Desc

Select Val1, Val2, RowNum
From
(
Select ID, Val1, Val2,
ID - (
Select Count(Cast(Tbl2.Val2 As VarChar))
From #GroupTestDesc As Tbl2
Where (Cast(Tbl2.Val2 As VarChar)) <
(Cast(Tbl1.Val2 As VarChar))
)
As RowNum
From #GroupTestDesc As Tbl1
) Tbl
Where RowNum <=3D 5


Rahul

SQL query - help needed - s

try this in sql server 2005
with A
as
(
select *,row_number() over(partition by baso order by labval)as rank
from <table>
)

select labval,baso from <table> where rank <=5

SQL query - help needed - s

try this in sql server 2005
with A
as
(
select *,row_number() over(partition by baso order by labval)as rank
from <table>
)
--small correction
select labval,baso from A where rank <=5

SQL query - help needed - Musy

Hi Rahul, this is really an intellingent piece of code.could you please help
me understand the logic,especially in the subquery, that is for the sql 2000.
thanks