asked on

SQL - Compare the top two rows in a query

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.

gigglick

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

josephdaviskcrm

ASKER

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

josephdaviskcrm

ASKER

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?

josephdaviskcrm

ASKER

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?

ASKER CERTIFIED SOLUTION

gigglick

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

josephdaviskcrm

ASKER

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

gigglick

No problem, glad I could help.