December 20, 2011 at 10:13 am
Ran accross this and can't figure it out. Why does this return 8 rows and not 7? I can limit the number of rows I put in the temp table and it returns what I expect, half of the records.
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE tempdb..#test
CREATE TABLE #test
(
PlayerIDINT,
fnameVARCHAR(25),
tierVARCHAR(30)
)
INSERT INTO #test VALUES (1, 'Brian', 'A1')
INSERT INTO #test VALUES (2, 'Jonathan', 'A1')
INSERT INTO #test VALUES (3, 'Jim', 'A1')
INSERT INTO #test VALUES (4, 'Stefan', 'A1')
INSERT INTO #test VALUES (5, 'Lisa', 'A1')
INSERT INTO #test VALUES (6, 'Carie', 'A1')
INSERT INTO #test VALUES (7, 'Janet', 'A1')
INSERT INTO #test VALUES (8, 'Mike', 'A1')
INSERT INTO #test VALUES (9, 'Keith', 'A1')
INSERT INTO #test VALUES (10, 'Peggy', 'A1')
INSERT INTO #test VALUES (11, 'James', 'A1')
INSERT INTO #test VALUES (12, 'Cathy', 'A1')
INSERT INTO #test VALUES (13, 'Lisa', 'A1')
INSERT INTO #test VALUES (14, 'Jen', 'A1')
INSERT INTO #test VALUES (15, 'Judy', 'B1')
INSERT INTO #test VALUES (16, 'Erin', 'B1')
SELECT TOP 50 PERCENT Playerid
FROM #test
WHERE tier = 'A1'
ORDER BY NEWID()
December 20, 2011 at 10:18 am
Can you show us the one that is failing? This query works fine for me.
Until then, I can speculate that there is rounding involved in the query that is failing for you. For instance if you change on of the 'B1' records to 'A1' then you will get 8 records in your final result set. This is due to rounding 50% of 15 up to 8 (since half of 15 is 7.5).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 20, 2011 at 10:57 am
The query isn't failing. It is just returning more records that it should. It returns 8 records but there are 14 records with a tier of A1. That should only return 7 records using TOP 50 Percent.
December 20, 2011 at 11:30 am
Returning an unexpected result is a query that fails. It may not generate an error, but the query still fails.
As I said in my previous post, the query as you provided works perfectly correct for me unless I alter the recordset.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 20, 2011 at 11:55 am
SQLRNNR (12/20/2011)
Returning an unexpected result is a query that fails. It may not generate an error, but the query still fails.As I said in my previous post, the query as you provided works perfectly correct for me unless I alter the recordset.
Have to agree. Ran the code as posted as well and it works for me also.
December 20, 2011 at 1:07 pm
So when running the query I provided you each get 7 records returned?
It could be an issue with the version of SQL. I need this to work on a SQL 2000 server. When I run it on SQL 2000 and 2005 I get 8 rows returned, which is incorrect. When I run it on a SQL 2008 server I get the correct result of 7 records.
December 20, 2011 at 1:27 pm
i can confirm that SQL2005 express is returning 8 rows, where my 2008 returns 7.
the only difference i see is in the execution plan; the layouts of the plans are the same, but the Sort operation returns 8 rows in SQL2005, but the 2008 Sort returns 7.
Lowell
December 20, 2011 at 1:37 pm
So it looks to have been a calculation and rounding error in earlier editions of SQL Server that is fixed. If you run the following, you will get the correct result. However, once you change to 50 it will show 8 rows again. The execution plan is showing that there are only 14 rows to query - so the only place it could have been was in the engine to calculate and round 50% incorrectly.
IF OBJECT_ID('tempdb..#test') IS NOT NULL
DROP TABLE tempdb..#test
DECLARE @percent DECIMAL (10,2)
SET @percent = 49.99
CREATE TABLE #test
(
PlayerID INT,
fname VARCHAR(25),
tier VARCHAR(30)
)
INSERT INTO #test VALUES (1, 'Brian', 'A1')
INSERT INTO #test VALUES (2, 'Jonathan', 'A1')
INSERT INTO #test VALUES (3, 'Jim', 'A1')
INSERT INTO #test VALUES (4, 'Stefan', 'A1')
INSERT INTO #test VALUES (5, 'Lisa', 'A1')
INSERT INTO #test VALUES (6, 'Carie', 'A1')
INSERT INTO #test VALUES (7, 'Janet', 'A1')
INSERT INTO #test VALUES (8, 'Mike', 'A1')
INSERT INTO #test VALUES (9, 'Keith', 'A1')
INSERT INTO #test VALUES (10, 'Peggy', 'A1')
INSERT INTO #test VALUES (11, 'James', 'A1')
INSERT INTO #test VALUES (12, 'Cathy', 'A1')
INSERT INTO #test VALUES (13, 'Lisa', 'A1')
INSERT INTO #test VALUES (14, 'Jen', 'A1')
INSERT INTO #test VALUES (15, 'Judy', 'B1')
INSERT INTO #test VALUES (16, 'Erin', 'B1')
SELECT TOP (@percent) PERCENT Playerid
FROM #test
WHERE tier = 'A1'
ORDER BY NEWID()
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 20, 2011 at 2:43 pm
Yes, that is what it appears to be. Interesting that if you use other quantities it sometimes returns the correct values. For instace, if you change the data so that 4, 6, 8, 10 or 12 records are all that is in the A1 tier then it returns the correct values. If you change them all to A1 then it returns the correct value. If you have 28 or 110 records as further examples it returns the incorrect number of rows.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply