We help IT Professionals succeed at work.

SQL - Compare the top two rows in a query

663 Views
Last Modified: 2010-04-21
SELECT TOP (6) CAST(AVG(Score) AS DECIMAL(6,1)) AS Score, MAX(Location) AS Location,
MAX(StoreID) AS StoreID,
CASE WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 90 AND 100 THEN 1 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 80 AND 89.9 THEN 2 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 70 AND 79.9 THEN 3 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 60 AND 69.9 THEN 4 ELSE 5 END AS Rating
FROM (
       SELECT ranking = dense_rank() over (PARTITION BY StoreID ORDER BY Date ASC), *
       FROM Structure WITH (NOLOCK)
       WHERE StoreID IN ('000001')
     ) a
GROUP BY ranking

The above query provides the following output:
Score            Location                                                  StoreID         Rating
69.0      3601 N.W. 27th Avenue                    000001      4
95.0      3601 N.W. 27th Avenue                    000001      1
82.0      3601 N.W. 27th Avenue                    000001      2
78.0      3601 N.W. 27th Avenue                    000001      3
78.0      3601 N.W. 27th Avenue                    000001      3
77.0      3601 N.W. 27th Avenue                    000001      3

What I need to do now is to compare the Ratings of the top two records.  For example, if they are the Same, or one is higher than the other.  If I am testing for both being the same, I only want the query to return data if they are both the same.  If they are different I would like the query to return no data.
Comment
Watch Question

Commented:
You could do....
begin
declare @table table(counter int identity(1,1),score float,location varchar(200),storeid bigint,rating bigint)
 
insert into @table(score, location, storeid,rating)
SELECT TOP (6) CAST(AVG(Score) AS DECIMAL(6,1)) AS Score, MAX(Location) AS Location, 
MAX(StoreID) AS StoreID,
CASE WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 90 AND 100 THEN 1 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 80 AND 89.9 THEN 2 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 70 AND 79.9 THEN 3 WHEN CAST(AVG(Score) AS DECIMAL(6,1)) BETWEEN 60 AND 69.9 THEN 4 ELSE 5 END AS Rating
FROM ( 
       SELECT ranking = dense_rank() over (PARTITION BY StoreID ORDER BY Date ASC), * 
       FROM Structure WITH (NOLOCK)
       WHERE StoreID IN ('000001')
     ) a 
GROUP BY ranking
 
declare @first float
declare @second float
 
select @first = score from @table where counter = 1
select @second = score from @table where counter = 2
 
if @first = @second
begin
select * from @table
end
 
end

Open in new window

Author

Commented:
Unfortunately, I don't think this is going to work for me.  I need something a little more strait forward.

Author

Commented:
Actually, on second glance, this may work for me afterall.  A couple of questions though...

First of all I needed to change

select @first = score from @table where counter = 1
select @second = score from @table where counter = 2

to this

select @first = Rating from @table where counter = 1
select @second = Rating from @table where counter = 2

because its Rating that I want to compare, not the score.  Being as how I've never messed with SQL like this before I wanted you to verify that doing so wouldn't mess anything else up.

Also, the output of the original query looks like...
      Score  Location                               StoreID    Rating
1    69.0    3601 N.W. 27th Avenue    000001    4
2    95.0    3601 N.W. 27th Avenue    000001    1
3    82.0    3601 N.W. 27th Avenue    000001    2
4    78.0    3601 N.W. 27th Avenue    000001    3
5    78.0    3601 N.W. 27th Avenue    000001    3
6    77.0    3601 N.W. 27th Avenue    000001    3

The output after this query runs however is...
    Score    Location                       StroreID  Rating
1    65    3601 N.W. 27th Avenue    1             4
2    69    3601 N.W. 27th Avenue    1             4
3    95    3601 N.W. 27th Avenue    1             1
4    82    3601 N.W. 27th Avenue    1             2
5    78    3601 N.W. 27th Avenue    1             3
6    78    3601 N.W. 27th Avenue    1             3

Note that not only are the scores not displaying the '.0' following, but they are a different set as well.  Also, storeID needs to retain is leading zeros.

Can  you help me further with this please?

Author

Commented:
Woops! nevermind the difference in score results, I figured out why I was having that problem... but can you tell me why I lost my zeros and how to fix that?
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry for my doubts. :-)  It actually works exactly how I need it to.  Thank you so much.

Commented:
No problem, glad I could help.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.