SELECT TOP 25 PERCENT Bug - Please, Need Urgent Response!
Mar 13, 2008
Script below returns 29 rows, must return 28!
Code Snippet
use master
select top 25 percent
id
from
(
select top 112
*
from
syscolumns
) d
order by
id desc
Script below returns 57 rows, must return 56!
Code Snippet
use master
select top 50 percent
id
from
(
select top 112
*
from
syscolumns
) d
order by
id desc
Reproducable on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
and
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Could anyone from MS SQL server team clarify what's happened and WHAT TO DO?
View 4 Replies
ADVERTISEMENT
Mar 15, 2008
I am writting a query to retrive some information about a person from the database, now intresting thing is that the query does'nt find the person who dont have a Last name in the database. i pass on the values which user have entered, the values are FirstName, LastName and EmployID, what ever information is given, its passed to the query and the person should be retrived if the person is logged in but problem is that the query works only if the person have a LastName in the database... this is my query.. please tell me what is wrong and what should i change.... its Urgent guys
SELECT distinct top 22 cfg_person.*,cfg_place.name,login_info.callablenumber,login_info_server.server
FROM cfg_person,cfg_login_info,cfg_agent_login,cfg_switch,login_info,cfg_place, login_info_server
where cfg_person.state='1'
and (cfg_switch.type = 5)
and cfg_person.is_agent=2
and cfg_person.first_name like 'Monique%'
and cfg_person.last_name like '%'
and cfg_person.employee_id like '%'
and cfg_person.dbid=cfg_login_info.person_dbid
and cfg_login_info.agent_login_dbid = cfg_agent_login.dbid
and cfg_agent_login.switch_dbid = cfg_switch.dbid
and login_info.person_dbid = cfg_person.dbid
and login_info.logged_voice=1
and cfg_place.dbid = login_info.place_dbid
and login_info.server_id = login_info_server.id
order by cfg_person.first_name
View 2 Replies
View Related
Jan 14, 2005
I have questions table and questions are categorized. I want to query say Top 10 questions but with some percentage based on category. For example, if I have 10 questions in category 1 and 50 questions in category 2 than when I select Top 10 with some percentage I want to come out more questions from category 2 than category 1.
View 6 Replies
View Related
Feb 23, 2006
Hi..
I have basic level question..
Why would we use SELECT TOP 100 PERCENT in a SQL Server 2000 query?
T.I.A
Papillon
View 12 Replies
View Related
Nov 13, 2007
Hi all
As you are aware we have TOP n [Percent] keyword that is used in Select statements ,I have a question??
When this keyword is evaluated by Sql-Server, after retreiving all Query-Result or upon the number of results reach to the specified number,sql-server stops
retrieving the rest of record.?
Thanks in advance.
regards.
View 3 Replies
View Related
Nov 25, 2005
I have a problem with the following query in SQLExpress:
SELECT TOP (100) PERCENT ClientSurname, ClientName
FROM dbo.Client
ORDER BY ClientSurname, ClientName
The query returns always assorted data ignoring the ORDER BY keyword, no matter if the query is invoked directly from Management Studio Express CTP as a View, Table-valued function or called from an Access ADP project. The result is always assorted.¨
Now an interesting thing is that the syntax below returns always an expected order:
SELECT TOP (99) PERCENT …
SELECT TOP 10000 …
Am I missing something or is it a bug in SQLExpress?
View 2 Replies
View Related
Jan 25, 2007
Hi, i wanted to take all the orderID that fall between a certain date. I was thinking of using this syntax... Dim sql as string = "SELECT orderID " & _
"FROM orders " & _
"WHERE orderDate BETWEEN 11/11/2007 AND 15/11/2007"
in my database i set the date to nvarchar, not date... so will this syntax works??
thx
View 14 Replies
View Related
Jan 8, 2002
I have two tables with the following data:
TableA: Serial_No
A
B
C
TableB: Serial_No
A
B
I need to retrieve the Serial_No in TableA but does not exist in TableB, in this case, data is "C".
I have tried the following Select statements:
1.
Select TableA.SerialNo
From TableA
Where TableA.SerialNo IN
(SELECT TableA.SerialNo
From TableB
Where TableA.SerialNo <> TableB.SerialNo )
2.
Select Distinct TableA.SerialNo
From TableA
Join TableB on
TableA.SerialNo <> TableB.SerialNo
However, the two statement gives me all data, i.e., A,B,C.
How should I discard the unwanted row?
Your help is greatly appreciated.
Thanks & Regards
MMC
View 3 Replies
View Related
Nov 1, 2007
my current stored procedure generates the following data
name type hours
Mike A 1
Mike A 2
Mike A 1
Bob A 1
Bob B 3
what i want is this:
name type hours
Mike A 4
Bob B 1
Bob B 3
and here is what i have
select departmet,name,sum(hours),
Case
when.... then .......
when.... then .......
when.... then .......
Else 'A'
End as Type
from tableA
Group by departmet,name
Any thoughts!
Thnks
View 8 Replies
View Related
Oct 10, 2005
Hi, I have a query as follows:select transactionDate, (select count(transID) from ......)I am having a problem which is that dates are not shown uniquely.For example,oct 1, 2005 00:12:00 - 12oct 1, 2005 01:30:00 - 12oct 1, 2005 01:13:00 - 12I want only to have:oct 1, 2005 01:13:00 - 12The select is working on time and not on a date, can you help me please?thank you.
View 1 Replies
View Related
Feb 15, 2002
Hi All,
I need a hand in this script.
SELECT a.id ,value = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
,value2 = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
FROM table_name a
where value > 0
ORDER BY VALUE
The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help!
Thank you in advance!
View 1 Replies
View Related
Apr 21, 2007
hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply
I am Looking for your reply !
View 1 Replies
View Related
Feb 6, 2008
Here is table dbo.vw_TimebyProjectAll
proj_name res_name yr SumOfHours
x Mike 2007 1
x Mike 2008 2
x Mike 2008 3
Here is table dbo._LastWeekTotalHours
proj_name res_name yr td_hours Week
x Mike 2007 1 1
x Mike 2008 2 8
x Mike 2008 3 9
now here is what the output should look like
Lets assume the user well enter 8 for week number
proj_name res_name totalHours LastWeekHours
x Mike 6 2
Y AJ .. €¦.
I am trying to write a select statement for that:
Here is what I have:
SELECT a.proj_name, a.res_name,a.TotalHours,g.TotalHours as LastWeekhours
FROM
Line 1( SELECT r.proj_name,r.res_name, SUM(r.td_hours) AS TotalHours FROM
line 2 dbo._LastWeekTotalHours as r, _vw_Employee as e
Line 3 WHERE e.RES_NAME = r.res_name and r.Week = 5 and r.yr = 2008
Line 4 GROUP BY r.proj_name, r.res_name ) as g
Line 5(SELECT r.proj_name, r.res_name, r.TotalHours
Line 6 FROM
Line 7 (SELECT proj_name, res_name, SUM(td_hours) AS TotalHours FROM
line 8 dbo._LastWeekTotalHours
line 9 GROUP BY proj_name, res_name ) r ,
line 10 (SELECT proj_name, res_name, SUM(SumOfHours) AS TotalHours FROM
dbo.vw_TimebyProjectAll
line13 GROUP BY proj_name, res_name )w
)a
if i excute line 1 - 4 i will get the last week hour correctly
if i excute line 5 - 13 i will get the totalhours correctly
my problem i can't put these two pieces work togather. feel free to re-write the select statment
thanks
View 14 Replies
View Related
Mar 16, 2004
Hi there friend...I'm in a midle of a program(vb.net). and i've got this problem, I've got do compare two tables from two diferent sql servers. Is this possible??? How can I do this???
Please, this is really urgent.
Thank you for your attention
View 2 Replies
View Related
Sep 26, 2001
I have a stored procedure in an Informix-database that returns a string. Its used in a SELECT statement like this.
SELECT t1.id, t2.id, sp_name(t1.id, t2.id) FROM table1 t1, table2 t2
I want to write it in SQLserver. I have tried this syntax but get error.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 't'.
SELECT t1.id, t2.id, dbo.sp_name(t1.id, t2.id, "") FROM table1 t1, table2 t2
Can I use stored proc in this way in SQL-server?
View 2 Replies
View Related
Jun 3, 1999
dear friend,
i run a sql command like this
select * from table_name
what i want is that if no. of records found are greater then 500 than it should stop the query and only show those 500 records dont go further and stop the process
waiting for reply
ashish bhatnagar
View 1 Replies
View Related
Oct 21, 2004
I have the following query where I am selecting the random row. However I need to select 10% randon rows from the tables. How do I do this?
select top 1 *
from table
ORDER BY NEWID()
View 1 Replies
View Related
Apr 2, 2004
I need rate to be a percent, it is returning 1 where XX=YY (e.g 100/100=1), but when the number (rate) is a fraction (90/100=.9)I am getting 0. Don't know what the problem is.
select XX as X, YY as Y, (YY/XX) as rate from blah where blah group by XX, YY order by XX
View 1 Replies
View Related
Nov 17, 2007
Hi all
whether using TOP clause in SELECT statement or [SET ROWCOUNT n] before SELECT statement, I want to know how SqlServer Behave?
whether Fetching data and then choosing n record of them or as soon as fetching n records , Sql Server Stops retrieving the rest of the data?
Thanks in advance.
Regards.
View 1 Replies
View Related
Dec 17, 1999
I'd like to understant the Percent Log Used behavior...
I monitored the Percent Log Used. For a specific database, it was 50 % used. I backup up the transaction log and the Percent Log Used was still 50 % used. For another database, the log was 60 % used. After the backup, it was 80 % used!!!!
Would you help me to understant this situation?
Thank you,
Fabio
View 2 Replies
View Related
May 2, 2008
Hi,
How can I convert my number results to Percent (%) with 2 decimal places?
Thanks
View 12 Replies
View Related
Jun 12, 2008
I am trying to take a string code seen below that gives me the income over a 12 month time frame but now I need to take this string and divide it by the acqcost. and it will not let me can you please take a look at this and see whats wrong.
(This string is what gives me the income)
=sum(Fields!Total.Value)-Sum(IIF(Fields!eqprecdt.Value< #6/1/2007#,Fields!bookvalue.Value* 0.07,Fields!bookvalue.Value* 0.07*-1 * datediff("d",#5/31/2008#,Fields!eqprecdt.Value)/365))
(This string is the acq cost)
SUM(Fields!acqcost.Value)
View 6 Replies
View Related
Mar 20, 2008
hai everybody,
i have doubt With the Top(n) percent keyword
for example consider the table employee with following details i'm using a query
SELECT TOP(19) PERCENT * FROM Employee
if i execute this query im gettin 2 record set whose id is less than
6
SELECT TOP(19) PERCENT * FROM Employee WHERE ID <6
like wise if i execute the above query i'm gettin only one record set instead of two record set ...
why so, can anyone help me on this??...
id name salary st_date city region
1Jason404201994-02-01 00:00:00.000New YorkW
2Robert144201995-01-02 00:00:00.000VancouverN
3Celia240201996-12-03 00:00:00.000TorontoW
4Linda406201997-11-04 00:00:00.000New YorkN
5David800261998-10-05 00:00:00.000VancouverW
6James700601999-09-06 00:00:00.000TorontoN
7Alison906202000-08-07 00:00:00.000New YorkW
8Chris260202001-07-08 00:00:00.000VancouverN
9Mary600202002-06-09 00:00:00.000TorontoW
Thanks in Advance
View 3 Replies
View Related
Mar 24, 2008
I a bit of newbie making reports and I cant figure out how to calculate the Percent of Total on a report. Assuming I have a field in my datatable called Color, I simply want to create a report that counts the occurences of each color and then perform a calculation as to what percent of the total that each of the colors make up. Basically a report that looks like this....
Color Count Percent
Red 50 25%
Blue 100 50%
Green 50 25%
Total 200
I have a table that has a header, a grouping row based on Color, and a footer. So using the report expressions, my table looks like this
Color Count Percent
=Fields!Color.Value =Count(Fields!Color.Value) WHAT EXPRESSION GOES HERE?!?!?!
Thanks!!!
Mike
View 5 Replies
View Related
May 8, 2008
I have a table of data, shown below that is composed of a scoreid, player id, and score. Using the following SQL Query:
"Select Top 50 Percent(score), playerid, scoreid from TScores
where playerid = 5
order by score"
I can get the lowest 50% of player number 5's scores.
How do I rewrite the query to give me each individual player's top 50 percent.
ScoreID
PlayerID
Score
10166
2
84
10167
2
80
14921
2
89
15069
2
95
13575
3
81
14282
3
84
12989
3
84
15821
3
89
16795
3
87
16950
3
85
17567
3
87
16948
5
79
16622
5
82
16590
5
85
11335
5
87
14279
5
82
13589
5
75
13973
5
77
15513
5
77
10070
5
81
10071
5
85
10049
6
86
10050
6
85
10051
6
98
16712
6
85
16710
6
85
View 8 Replies
View Related
Jul 20, 2005
Hi Everyone,All the very best for 2004!!i need urgent help with this problem, the users are about to skin mealive!!we have an access front end with linked to sql server 2k tables.when a user tries to insert a record into one of the tables it"randomly" returns a generic ODBC error and fails to save. on otheroccasions the same record will save.a trace was applied and the following select seemed to appear rightafter the insert statement whenever it failed:select substring('NY',status/1024&1+1,1) from master..sysdatabaseswhere name=DB_NAME()i had a look at other articles in the groups re this select statement,but could not find a clear answer.i have tried the insert statements as both SQL pass throughs and justplain docmd.runsql'scan someone help me with the following:* what is the purpose of the select?* what other investigations can i do to get more info on why thisshould be happening?* how can i stop it?the table i am doing the inserts into is showing as have a numericdata type field in sqlserver, but the linked table shows this numericfield as text - could this be the problem?? this field is not used inthe insert statement.i could not find any references in the MS knowledge base.any and all help would very gratefully received.Edwinah63
View 2 Replies
View Related
Jun 14, 2004
I have a script file which do the replication and in the script there is a lot of table need to be addad as article.
So while use osql execute the script there would be cost a lot of time. What I want to do is show a dialog which could show the progress percent and the file name which is now being addad as article.
hwo can i use Transact-SQL to do this?
Thanks a lot
View 4 Replies
View Related
Dec 24, 2013
I am trying to use this command to change the variable in where clause each time:
psexec servername -E cmd.EXE /c "sqlcmd -S servername /E -d dbname -v entertext="'%anything%'" -i c:myfoldermyscript.sql -o c:mypathoutput.sql"
myscript.sql is like:
select * from table_name
where summary like $(entertext);
This command works just for variables with single and exact word. It does not work for variables which has space between them or when I enter part of summary.In fact, I wanted to write something which the user be able to enter variable each time when he/she run this batch file. because I put this sqlcmd command in batch file( like this in oracle: "select * from table_name where summary = &entertext; " I was looking the same command in sql server , but I did not find it). I do not know .net or any other programing language( I know just csh and command line). I want to do this just with sql command.
View 3 Replies
View Related
Sep 26, 2013
I have a question about rounding and converting to percentage and adding in the '%'..This is my original code
SELECT * FROM
( SELECT Baby,
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) as BabyValue
FROM NewBorn
WHERE Category = 'Boy'
[code]....
But I also need my data to have the '%' and it should have 2 decimal place which is as below, I have tried to make it this way
CAST( CAST( SUM(TotalBaby) AS DECIMAL )/ CAST( SUM(TotalParent) AS DECIMAL) AS DECIMAL(10,4)) * 100 + '%' as BabyValue
But it prompt me the error 'Error converting data type varchar to numeric.'
A B C D
0.22%0.29%0.11%0.32%
View 2 Replies
View Related
Dec 29, 2014
I am using SQL Server and have a table that is currently set to decimal(5,2) as the datatype. I am using Tableau as my front-end and if I create a dashboard, I can see my fields like 65.25, 65.72, etc. I want to view them as 65.25% however when I change the data type in Tableau to a %, it shows up as 6525.25%. Is there a way in the SQL Server side to change the column so it shows as a percent? I am sure the answer is no because I have never seen that. Only seen decimal values. Maybe I need to change it to decimal 2.2 if that even exists.
View 3 Replies
View Related
Jan 17, 2008
I have a data containing salesman who can belong to groups. The groups have "split" values but the splits are not on a percent basis. Can I create a query to get the percentage in a column? Please look at the table below and my attempted query.
Salesman GroupSplitSplitPercent
AG110.33
BG110.33
CG110.33
YG20.50.5
ZG20.50.5
UPDATE Splits
SET SplitPercent = Split / (SELECT SUM(Split) FROM Splits GROUP BY Splits.Group HAVING Splits.Group = ???)
View 2 Replies
View Related
Jul 20, 2005
Hi!How can I get percent of executed procedure in MSSQL Server, lik inEnterprise Manager and/or dbMgr2k ?I use Visual C# and MSDE.Thank's for help, gregory
View 1 Replies
View Related
Nov 15, 2007
Hello,
This is my issue. I have values in my table that I need to format as a percent. So when I use the FormatPercent() function, it works but if one of the records has no value for that field. It generates a #Error message in that textbox. I tried using an if statement and that didn't work either. Someone please help me with this. Any information is appreciated.
View 17 Replies
View Related
