Bug In SELECT TOP (100) PERCENT With ORDER BY In SQLExpress?
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
ADVERTISEMENT
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
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
View Related
Mar 27, 2008
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
View 5 Replies
View Related
Nov 17, 2006
Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.
My query would look something like this:
Select leadershipName
From Leadership
Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:
Executive Board Members, Delegates, Grievance Chairs, and Negotiators
My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?
Thanks,
Zoop
View 1 Replies
View Related
Aug 15, 2006
I'm doing a INSERT...SELECT where I'm dependent on the records SELECT:ed to be in a certain order. This order is enforced through a clustered index on that table - I can see that they are in the proper order by doing just the SELECT part.
However, when I do the INSERT, it doesn't work (nothing is inserted) - can the order of the records from the SELECT part be changed internally on their way to the INSERT part, so to speak?
Actually - it is a view that I'm inserting into, and there's an instead-of-insert trigger on it that does the actual insertions into the base table. I've added a "PRINT" statement to the trigger code and there's just ONE record printed (there should be millions).
View 3 Replies
View Related
Mar 13, 2007
I can't start SQLEXPRESS.
The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".
Any thoughts why this might be and how I can fix this?
View 3 Replies
View Related
Sep 21, 2006
hiya,
I have sqlExpress and sqlServerManagementStudio on my XP pro box.
Will the installation of sqlExpress (Advanced Services) cause any problems?IS thereanything that I shold be aware of in advance?
many thanks,
yogi
View 3 Replies
View Related
Jan 19, 2007
Hi All,
I have created an installation application which will install the application with SQL Express and .NET Framework 2.0.
If i install this application in a Fresh system(i.e which is not having SQL Express), it is installing the application with new database instance successfully.
But if i try to install the same in a system which is already having SQL Express, throwing "Object reference exception" because it is not able to create the new database instance.
Can anybody help me out .
Regards,
Doppalapudi.
View 2 Replies
View Related
Aug 24, 2007
If I return top 25 with an order by, will it get the whole result then take top 25 and order it or will it just return the first 25 it finds and order it. select top 25 c.firstname, c.lastname, o.units, o.partnum
from customer c
inner join orders o
on c.key = o.key
where o.units > 500
order by partnum
View 1 Replies
View Related
Jul 20, 2005
I have a Microsoft SQL Server 7.0.I wrote a sql command that creates a temporary table with a ORDER BYclause.When a execute a SELECT on this temporary table sometimes the result isok, but sometimes is not ordered. I didn´t see anything like that. Anyclue?Is there any kind of limits with temporary tables ? Because the commandthat creates the temporary table is working and the rsults is alwaysordered. But when I create a table with it, sometimes the table is notordered.Paulo*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 2 Replies
View Related
Mar 21, 2006
Because of numerous problems trying to get sqlexpress working, I uninstalled it with the intention of reinstalling (via Add or Remove Programs). However, now when I try to reinstall it, I get a message that the I am not making a changes so it won't let me install it.
I do have sql server 2005 developer's edition installed; is that the reason? and does that mean I cannot have both installed on the same machine?
View 1 Replies
View Related
Aug 31, 2007
i have 2 selects:select * from view_veiculos where nome_marc like '%fiat%' and ano='2001'union select * from view_veiculos where nome_marc like '%fiat%'when i execute it on sql server, i get the following results:id 1 _______ ano 2004id 2 _______ ano 2001the row with ano 2004 is before the row with ano 2001the problem is that id like it to be ordered following the select order, which means that 2001 should be displayed before 2004,like that:id 1 _______ ano 2001id 2 _______ ano 2004all the results from the first select from the query need to be placed before the results from the second query.how can i make it ?thanks for all
View 23 Replies
View Related
May 6, 2008
Hi
I have a query which returns some rows.. what happens if i use a select distinct instead of a select.. this is my sproc
DECLARE @Counter TABLE(
PlanId int,
FundId int,
ClientFundName varchar(110),
DisplayOrder int IDENTITY(1,1),
IsDefault bit,
IsPortfolioFundOnly bit
)
INSERT INTO @Counter
(
PlanId,
FundId,
ClientFundName,
IsDefault,
IsPortfolioFundOnly
)
SELECT
5923,
f.FundId,
d.FundName,
CASE WHEN d.FundDefault IS NULL THEN 0 ELSE 1 END,
CASE WHEN Lower(p.FundType) = 'modfundonly' THEN 1 ELSE 0 END
FROM
PlanDetail d
INNER JOIN Statements..Fund f
ON d.CUSIP = f.CUSIP
OR
d.Ticker = f.Ticker
OR
d.Ticker = f.ClientFundId
OR
d.CUSIP = f.ClientFundId
-- Do an internal join on the PlanDetail table to get the value of the FundType to derive whether
--fund can only be chosen as part of a portfolio.
LEFT JOIN PlanDetail p
ON d.FundName = p.FundName
AND
d.PortfolioName = p.PortfolioName
WHERE
d.PlanNumber IS NOT NULL
AND
p.PortFundPercent IS NULL
GROUP BY
f.FundId,
d.FundName,
d.FundDefault,
--d.PlanNumber,
--d.Cusip,
-- d.Ticker,
--d.RowNumber,
p.FundType
ORDER BY
Min(d.PlanNumber),
Min(d.RowNumber)
any help will be appreciated.
Thanks
Karen
View 1 Replies
View Related
Oct 6, 2005
I get the "ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
SELECT DISTINCT
pdm.Account,
pdm.Customer
FROM
dbo.Demands pdm LEFT OUTER JOIN
dbo.Tickets rct ON pdm.Account = rct.Account
WHERE
pdm.Code IN (66, 51)
ORDER BY
pdm.TransactionDate DESC
Is there any way to make the ORDER BY work in this case?
View 1 Replies
View Related
Jan 20, 2014
I have table called 'UserDetails'. If I execute below select query it should display in order of uno= 7,13,5 but i get in order of
uno=5,7,13.
How to get in order of uno= 7,13,5
Select EmailAddress,EmployeeName,UNo, MobileNumber
from UserDetails where (UNo=7 or UNo=13 or UNo=5 ) group by uno,emailaddress,employeename,uno,mobilenumber
Result I am getting as
EmailAddress EmployeeName UNo MobileNumber
-----------------------------------------------------------
aaa@xxx.com ravi 5 8989898989
bbb@xxx.comramesh 79898989898
ariv@gmail.com arivu 13 8989898989
View 3 Replies
View Related
Jul 23, 2005
If i have two identical queries, with exception of top criteriaSelect top 1 * from photosSelect top 8 * from photoswhy does altering the top critieria alter the order of the returnedresults?
View 1 Replies
View Related
Mar 27, 2006
I have the following basic statements being executed:Create a temp table, #TempPagingInsert Into #TempPaging (Col1, Col2)Select Col1, Col2 From SomeOtherTable Order By Col2, Col1Select * from #TempPagingI can't provide a reproduceable scenario right now without making thisinto a 200K post, so I'm hoping someone will know what the issue is.. Ican't find anything regarding this in BOL.Basically the order is off a little bit every now and then when thereare large amounts of data, like more than a couple hundred rows. Isthere something I need to do to guarantee the originally selectedorder?This is very important that it be in order from the original selectstatement as this is for paging. Adding an order by in the secondselect from the temp table will not fix the problem. In this particularinstance that I have reproduced we are using SQL 2005 but have alsoseen this on SQL 2000 servers. I had previously asked this question asI was using a SELECT INTO statement, but now we are manually creatingthe temp table (Pain in the ass) and still having the same issue. Bestcase scenario is for it to work for a SELECT INTO.Any ideas?
View 29 Replies
View Related
Jul 20, 2005
Which is more efficientWhere NonindexedColumn=x and IndexedColumn=yorWhere IndexedColumn=y and NonindexedColumn=xor does matter? Will the optimiser work it out?(I'm building the SQL string on the fly in the fron-end)
View 1 Replies
View Related
Jul 20, 2005
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
View 3 Replies
View Related
Apr 25, 2008
Hello there,
I have two tables I selecting name using like with %string% from the two tables but I need to order the result comes from the two table:
1- the exact match for the search string come first from the two table.
2- and the partial match comes last after the exact match.
this is my DDL for the two tables :
USE [Northwind]
GO
/****** Object: Table [dbo].[Person] Script Date: 04/25/2008 14:33:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[PersonID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
second table:
USE [Northwind]
GO
/****** Object: Table [dbo].[Members] Script Date: 04/25/2008 14:33:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Members](
[MemberID] [int] NULL,
[Type] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Email] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
and this my search query I have it in a stored Proc.
select *
from
(
SELECT PersonID, Type, Name, email
FROM Person
WHERE (Name LIKE '%'@Name'%')
union all
SELECT PersonID, Type, Name, email
From Members
WHERE (Name Like '%'@Name'%' )
) Y
Order by
Case[Name] when @Name Then 1 Else 2 End,
Case[Name] when 'm' Then 1 Else 2 End
Thank you for your time
Sms
View 5 Replies
View Related
Aug 1, 2006
Hi,
This may sound like a dumb question, but I need to be certain of the answer.
If I have a query like this:
INSERT INTO table1
(col1, col2, col3, ... )
SELECT col4, col5, col6, ...
FROM table2
ORDER BY col7, col8, col9, ...
and table1 has an identity column that increments by 1 each time, am I gauranteed that the records inserted into table1 will always be inserted in the order as specified in the ORDER BY clause and hence the increasing identity column in table1 will reflect the same order as that of the ORDER BY clause?
Seems like it should be the case but I need to be sure.
Thanks,
Peter
View 11 Replies
View Related
Dec 23, 2005
Hi,
I need to select few items from sql database.I know for ORDER BY, but I need those items to be mixed in random order every time when they are returned from database. Those are the same items every time, just randomly mixed.
Can i do it with SQL, or I have to find another way (e.g. to mix them after sql returns them)?
View 1 Replies
View Related
Nov 26, 2007
I am trying to display items from a table that have a type. For each condition there are between 7 and 10 types. I am looping through each type and displaying all items in that type. I am using DISTINCT to pull the types and count them, so I know how many there are and how times to loop. My problem is that DISTINCT is ordering the types alphabetically! I want them in the same order they went into the table. I tried adding ORDER BY primaryID, but got an error that said I also had to select primaryID as well as type, so now I am selecting every item that fits the condition and not just the DISTINCT types! Is there any way to make it order by column_position? I am using SQL 2K.
View 9 Replies
View Related
Feb 21, 2014
I am assigning temporary Ids in my table, and right now I am assigning new ones via find the MAX(Temp_Id), stripping out the number and adding one.
Since these are temporary I would like to be able to reuse older ones when they are no longer in the table.
For example I have:
ID0001
ID0002
ID0003
ID0006
My code right now will add ID0007. I would like to re-use ID0004 and ID0005 since they're no longer in use. How do I go about do this?
View 3 Replies
View Related
Nov 6, 2005
Is there a shortcut to spelling out column names when you are doing a select statement?
For instance could you write Select 1, 5, 6 from table where whatever...
I tried this but didn't get any results so if you can I must be using wrong syntax.
Thanks
!
View 2 Replies
View Related
Mar 13, 2008
Hello,
I'm trying to CONVERT some DateTime column to VarChar in the SELECT part but still do a DateTime ORDER BY while using a UNION.
In other words, does anyone know how to make this work:
create table #temp1 (
d datetime
)
create table #temp2 (
d datetime
)
insert into #temp1(d)
select '2001-12-12' union
select '2002-11-11'
insert into #temp2(d)
select '2003-10-10' union
select '2004-09-09'
--works fine-------------------------------
select convert(varchar, d, 101) dd from #temp1
order by d
-------------------------------------------
--but can't make this work-------------------
select convert(varchar, d, 101) dd from #temp1
union select convert(varchar, d, 101) dd from #temp2
order by dd -- wrong type of sorting
--order by d-- error
--order by cast(dd as datetime) -- error
-------------------------------------------
drop table #temp1
drop table #temp2
I would really appreciate your help.
Thanks in advance,
Thomas
View 5 Replies
View Related
Jul 20, 2005
According to BOL, columns in an ORDER BY clause do not have to be in the SELECTcolumn list unless the SELECT includes DISTINCT, or the UNION operator.Is this a SQL Server thing, or SQL standard behavior? That is, if I were to writeabsolutely pure SQL-92, must columns in the ORDER BY clause be present in the SELECTlist?
View 1 Replies
View Related
Jan 3, 2008
I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,
I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.
Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?
Here is how they exist in the physical table:
CLIENTID CHANGEDATE NEWVALUE
------------ ----------------------- ----------------------------------------------------------------
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 NULL
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
Here is how they exist after being inserted into the temp table using DISTINCT:
ID CLIENTID CHANGEDATE NEWVALUE
----------- ------------ ----------------------- ----------------------------------------------------------------
15 C0HTRA200005 2007-11-19 21:07:20.000 NULL
16 C0HTRA200005 2007-11-19 21:10:52.000 NULL
17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition
18 C0HTRA200005 2007-11-19 21:11:57.000 NULL
19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition
20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition
I've tried this many different ways using variations of ORDER BY/ no ORDER BY.
I've tried creating the temp table first with the IDENTITY field, then insert into it.
I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.
Any ideas would be huge.
Thanks much.
View 5 Replies
View Related
Apr 26, 2007
Say I have a result set with two fields numbers and letters.
1 A3 A1 B2 B
The result set is ordered by the letters column. How can I select the distinct numbers from the result set but maintain the current order? When I tryselect distinct Number from MyResultSet
it will reorder the new result set by the Number field and return
123
However, I'd like maintain the Letter order and return
132
View 1 Replies
View Related
Sep 15, 2007
Hi,
Check this SQL
SELECT DISTINCT
TblOrder.CustomerUID,
TblOrder.OrderHiddenID,
TblPayment.PaymentAmount,
TblPayment.Result,
TblOrder.OrderID
FROM TblOrder
CROSS JOIN TblPayment
WHERE (TblOrder.CustomerUID = @IsCustomerID)
AND (TblOrder.OrderHiddenID = @IsHiddenID)
AND (TblPayment.Result = 'Pending')
AND (TblOrder.OrderID IN (SELECT MAX(TblOrder2.OrderID) FROM TblOrder TblOrder2))
one customer can have more than one orders.
So i need to select customer last inserted order details from database.So when i use above sql i returns null.what might be the reason for this
View 4 Replies
View Related
