February 4, 2009 at 7:59 am
Hi all,
I met one problem on how to sort recordset. Database: SQL Server 2000
Simple Table structure: Rating,BooKID.
Sample Data: 97,123; 96,456, 95,123;90,123,95,456.
The result should like the following:
97,123
95,123
90,123
96,456,
95,456
Please help me to solve it. thanks
February 4, 2009 at 9:10 am
This works in 2005, don't think it's a new feature... 🙂
create table #t (r int, b int)
insert #t values(97,123)
insert #t values(96,456)
insert #t values(95,123)
insert #t values(90,123)
insert #t values(95,456)
select r,b from #t order by b asc, r desc
Derek
February 4, 2009 at 9:19 am
Maybe I didn't explain clearly my request.
I will give another sample.
Create table #t(rating int,bookid int)
insert into #t
select 97, 201
union all
select 96,100
union all
select 95,300
union all
select 50,100
union all
select 10,201
union all
select 30,300
need result:
97,201
10,201
96,100
50,100
95,300
30,300
February 4, 2009 at 9:36 am
You need to find the max rating for each bookid. Then you can use this number as the first key in the order by clause. Here is an example:
select rating, t.bookid
from #t as t inner join (select max(rating) as MaxRating, bookid
from #t
group by bookid )as dt on t.bookid = dt.bookid
order by dt.MaxRating desc, t.bookid, rating desc
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2009 at 9:41 am
You need to explain the requirements better. Looking at your expected results doesn't really make much sense.
February 4, 2009 at 10:05 am
Adi Cohn (2/4/2009)
You need to find the max rating for each bookid. Then you can use this number as the first key in the order by clause. Here is an example:select rating, t.bookid
from #t as t inner join (select max(rating) as MaxRating, bookid
from #t
group by bookid )as dt on t.bookid = dt.bookid
order by dt.MaxRating desc, t.bookid, rating desc
Adi
Thanks a million. It works perfect. I will use this in my production code.
February 4, 2009 at 11:03 am
Could you explain what the result order is? I could make a guess, but would prefer if you stated it before I try to reproduce it.
Derek
February 4, 2009 at 10:15 pm
For each bookID there were few records with different rating. The op wanted to first key in the order by clause to be the maximum rating that the same book ID had, so if we had the fallowing:
ID Rating bookID
1 74 1
2 78 1
3 59 2
4 86 2
The order should be that bookID 2 will be first because it has the maximum rating. Even row number 3 will be before rows 1 and 2, because the order is done according to the maximum rating that is grouped by the bookID (86 for bookID 2) and not according to the row’s rating. The secondery key in the order by is the bookID (for cases that there are different bookIDs that have the same maximum rating). The last key in the order by was the specific rating that the row had.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 4, 2009 at 11:03 pm
Then you need to exactly what they ask.
1st, figure out highest rate for each book:
(Select MAX(Rating) as TopRating, BookId
from dbo.BooksRatings
group by BookId ) A
2nd, you need to retrieve all records for each book:
INNER JOIN dbo.BooksRatings B ON B.BookID = A.BookID
After that you add SELECT on top of the query - you know better what to return.
And at last, define the ordering:
ORDER BY A.TopRating DESC, A.BookID, B.Rating DESC
Isn't it easy when you follow the orders?
🙂
_____________
Code for TallyGenerator
February 5, 2009 at 6:48 am
I don't know where you guys determined that the order was by max rating descending first. Nothing that Judy posted mentioned this.
My guess, from her examples, was that she wanted the result to be by order of first occurrence of bookid and then by rating in descending order. This would actually need additional information but satifies both of her examples.
Hence the request for a specification of the required output rather than just examples.
However, you seem to have correctly guessed what she wanted.
Derek
February 6, 2009 at 9:38 am
Thanks all of you!
I am sorry to reply late because I am kind of busy on cluster configuration.
What I want is just order by rating descending, but if there are other records which has same bookID with low rating, It must be displayed together with its high rating and order by bookid descending as well.
February 8, 2009 at 3:46 am
i thing derek is right
order by f1 desc , f2 asc
works...
February 9, 2009 at 4:05 am
No. From Judy's description, Adi's solution is needed.
Although I still don't know how he guessed that what what was needed from the sample data since my solution would produce the correct result for both examples given. But it won't work on a more complicated solution such as this.create table #t (rating int, bookid int)
insert #t values(97,123)
insert #t values(96,456)
insert #t values(95,123)
insert #t values(90,123)
insert #t values(95,456)
insert #t values(98,789)
insert #t values(90,789)
select r,b from #t order by bookid asc, rating desc
/*
rating bookid
----------- -----------
97 123
95 123
90 123
96 456
95 456
98 789
90 789
*/
select rating, t.bookid
from #t as t inner join (select max(rating) as MaxRating, bookid
from #t
group by bookid )as dt on t.bookid = dt.bookid
order by dt.MaxRating desc, t.bookid, rating desc
/*
rating bookid
----------- -----------
98 789
90 789
97 123
95 123
90 123
96 456
95 456
*/
Apparently, Judy wants the second result set.
Derek
February 9, 2009 at 4:18 am
Tray this
simple
Create table #t(rating int,bookid int)
insert into #t
select 97, 201
union all
select 96,100
union all
select 95,300
union all
select 50,100
union all
select 10,201
union all
select 30,300
select a.rating,b.bookid from #t a, #t b
where a.rating = b.rating
order by
b.bookid asc ,
a.rating desc
RESULT;
RI BI
96 100
50 100
97 201
10 201
95 300
30 300
February 9, 2009 at 11:55 am
arun.sas (2/9/2009)
Tray thissimple
Create table #t(rating int,bookid int)
insert into #t
select 97, 201
union all
select 96,100
union all
select 95,300
union all
select 50,100
union all
select 10,201
union all
select 30,300
select a.rating,b.bookid from #t a, #t b
where a.rating = b.rating
order by
b.bookid asc ,
a.rating desc
RESULT;
RI BI
96 100
50 100
97 201
10 201
95 300
30 300
The result you provided is not that I wanted. the result what I wanted is like below.
RESULT;
RI BI
97 201
10 201
96 100
50 100
95 300
30 300
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply