Top 3 and next 2 - 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

Top 3 and next 2

Author

Topic

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 09:49:26

Hai friends,

This is the Table MdRating .This has rating column

i need top 3 rating from the table and the next 2 top rating from the table.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 09:50:51

eh? ...so just top 5?

perhaps you should give us some sample data

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 09:56:03

top 3 next 2 what does that mean? do you mean top 3 in decreasing and top 2 in increasing?

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:00:02

ya top 5, but i need to get the top3 in one query and the next 2 in another query.

For example,
if the rating column has data
1.10
2.62
3.82
4.76
5.88
6.58
7.41
i need top 3 answers which are10,62,82 and next 2 answers such as 76,88

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:01:22

so top 5 based on what? is that an ID column?

Em

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:04:56

order by rating desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:05:02

quote:
Originally posted by Kotti

ya top 5, but i need to get the top3 in one query and the next 2 in another query.

For example,
if the rating column has data
1.10
2.62
3.82
4.76
5.88
6.58
7.41
i need top 3 answers which are10,62,82 and next 2 answers such as 76,88

SELECT TOP 3 ID,Answer FROM Table ORDER BY ID

SELECT TOP 2 Answer FROM Table WHERE ID NOT IN
(SELECT TOP 3 ID FROM Table ORDER BY ID)
ORDER BY ID

didnt understand the need of seperate queries though

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:06:25

but in your example data the top3 and next top 2 you wanted are not based on rating descending?

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:06:43

quote:
Originally posted by Kotti

order by rating desc

then how do you think 88 will come after 76?

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:08:22

i just send an example

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:09:31

quote:
Originally posted by Kotti

i just send an example

but it's NOT an example, it's just random nonsense??

anyway... visakh's example still holds just order it by what you want instead of ID

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:11:46

quote:
Originally posted by Kotti

i just send an example

do you mean an example of how the output should not be? Your description and example dont match.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:14:38

i will send once again
For example,
if the rating column has data
1.10
2.62
3.82
4.76
5.88
6.58
7.41

i need top 3 answers which are 88,82,76 in one query
Then next top 2 which are 62,58

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:16:15

then use visakh's example and change the order by....?

Em

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:17:04

oh it's you... hi

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107130

Em

Sinisa
Starting Member

5 Posts

Posted - 2008-07-24 : 10:17:09

Something like this:
-- top 3
SELECT Top 3 X
FROM TAbleX

-- and next 2
SELECT Top 2 A.X
FROM
(
SELECT X
FROM TAbleX
)A LEFT OUTER JOIN
(
SELECT Top 3 X
FROM TAbleX
)B
ON A.X=B.X
WHERE B.X is null

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:18:19

No, you don't have an order by clause

quote:
Originally posted by Sinisa

Something like this:
-- top 3
SELECT Top 3 X
FROM TAbleX

-- and next 2
SELECT Top 2 A.X
FROM
(
SELECT X
FROM TAbleX
)A LEFT OUTER JOIN
(
SELECT Top 3 X
FROM TAbleX
)B
ON A.X=B.X
WHERE B.X is null

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:18:47

can't get the exact answer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 10:19:02

quote:
Originally posted by Kotti

i will send once again
For example,
if the rating column has data
1.10
2.62
3.82
4.76
5.88
6.58
7.41

i need top 3 answers which are 88,82,76 in one query
Then next top 2 which are 62,58

try it yourself by modifying from what i provided earlier.

Kotti
Posting Yak Master

129 Posts

Posted - 2008-07-24 : 10:38:24

Tried ,not getting the answer

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 10:39:02

show us what you tried

Em

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources