How To Get Row From Select Query In Order
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
ADVERTISEMENT
Mar 22, 2008
Hi,
I wonder if its possible to perform a ORDER BY clause in an SELECT DISTINCT sql query whereby the AS SINGLECOLUMN is used. At present I am recieving error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified. My guess is that I cant perform the Order By clauses because it cant find the columns individually. It is essentail I get this to work somehow...
Can anyone help? Thanks in advance
Gemma
View 10 Replies
View Related
Jan 21, 2008
I am attempting to sort the results of a query executed against a table variable in descending order. The data is being inserted into the table variable as expected, however when I attempt to order the results in descending order, the results are incorrect. I have included the code as well as the result set.
DECLARE @tblCustomRange AS TABLE
(
RecordID INTEGER IDENTITY(1,1),
RangeMonth INTEGER,
RangeDay INTEGER
)
DECLARE @Month INTEGER
DECLARE @Day INTEGER
-- Initialize month and day variables.
SET @Month = 8
SET @Day = 11
-- Insert records into the table variable.
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,2)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (1,27)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (6,10)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (9,22)
INSERT INTO @tblCustomRange
(RangeMonth, RangeDay) VALUES (12,16)
-- Select everything from the table variable ordering the results by month, day in
-- descending order
SELECT * FROM @tblCustomRange
WHERE (RangeMonth < @Month) OR
(RangeMonth = @Month AND RangeDay <= @Day)
ORDER BY RangeMonth, RangeDay DESC
I am getting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
2 1 27
1 1 2
3 6 10
I am expecting the following resultset:
RecordID RangeMonth RangeDay
----------- ----------- -----------
3 6 10
2 1 27
1 1 2
View 1 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
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
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
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
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
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
Nov 3, 2003
I have the following records,
MyDate MyId
2003/10/25 2
2003/10/26 3
2003/10/26 1
2003/10/27 3
2003/10/28 2
2003/10/29 4
I want to get the most earlier date distinct records.
I try to use "SELECT DISTINCT MyID from Table;"
I expect to get Myid: 2,3,1,4
But the computer returns Myid:1,2,3,4
Is there a way to get the records using
"SELECT DISTINCT MyID from Table ORDER BY MyDate;"
Appreciate help......
View 6 Replies
View Related
Jan 20, 2005
I'm new to SQL stored procedures, but I'm looking to be able to select order by statement.
declare @OrderBy
@OrderBy = 'first_name'
Select first_name,last_name from table1
Union
Select first_name,last_name from table2
if @OrderBy = 'first_name' begin
Order By first_name
else
Order By last_name
end
You'll have to excuse my if statement if the syntax is incorrect (used to only writing asp ifs :P). Thanks for any help
View 6 Replies
View Related
Feb 7, 2008
Hi,
I'm trying to look for an efficient way to select records from two tables, combine them (not just one set above the other) and also efficiently page the results as well as dynamically order by specific columns. So far I have this....
DECLARE @Temp TABLE
(
IDINTNOT NULL,
NameNVARCHAR(128)NOT NULL,
TypeCHAR(1)NOT NULL
)
INSERT INTO @Temp
SELECT i.ID, i.Name, Type = 'I' FROM Item i
UNION
SELECT p.ID, p.Name, Type = 'P'FROM Package p
SELECT * FROM @Temp
ORDER BY Name ASC
I was going to then implement some sort of of ROW_NUMBER like paging and ordering on the @Temp table variable. Problem is there could be potentially 1000's or more Items and Packages and they would all go in this single Temp table before having records 1 to 10 returned. Is there a more efficient way of doing this before I proceed any further?
Many thanks for any help! :)
View 10 Replies
View Related
Jul 23, 2005
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View 18 Replies
View Related
Nov 28, 2007
Hi.
We are now working with SQL2000sp4, planning migration to SQL2005 in few months though.
I've faced performance issues with large tables (200-500 mln rows, 50-100Gb of data+indexes)
New data are uploaded into tables once a day, around 1mln rows. Thats the only time of inserting data, during daytime tables are used for SELECTs only.
The problem that daily INSERTs are taking too much time now, because of rebuilding few indexes for the table.
I noticed that partitioning solution looks like solving this problem well. So i splitted master data table into 4 tables:
old master table:
CREATE TABLE [dbo].[DTB] (
[report_date] [smalldatetime] NOT NULL ,
[param] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[param_value1] [decimal](18, 2) NULL ,
[param_value2] [decimal](18, 2) NULL ,
[param_value3] [smallint] NULL
)
CREATE INDEX [IX_DTB_DT_ACC] ON [dbo].[DTB]([report_date], [param])
new partition1:
CREATE TABLE [dbo].[be_data_DTB_part_2007_q1] (
[report_date] [smalldatetime] NOT NULL ,
[param] [char] (10) COLLATE Cyrillic_General_CI_AS NOT NULL ,
[param_value1] [decimal](18, 2) NULL ,
[param_value2] [decimal](18, 2) NULL ,
[param_value3] [smallint] NULL ,
CONSTRAINT [CK_be_data_DTB_part_2007_q1_report_date] CHECK ([report_date] >= '2007-Jan-01' and [report_date] <= '2007-Mar-31')
)
CREATE CLUSTERED INDEX [idc_be_data_DTB_part_2007_q1_report_date_param] ON [dbo].[be_data_DTB_part_2007_q1]([report_date], [param])
Similar are definitons for other partitions - q2, q3 and q4.
And here is partitioned view itself:
create view dbo.data_DTB
as
select * from dbo.be_data_DTB_part_2007_q1
union all
select * from dbo.be_data_DTB_part_2007_q2
union all
select * from dbo.be_data_DTB_part_2007_q3
union all
select * from dbo.be_data_DTB_part_2007_q4
I want users to access data SELECTing from view data_DTB, while I perform daily inserts right into be_data_DTB_part_2007_q4.
In general, this solution works well. For example:
Code Block
set statistics profile on
go
select * from data_DTB where report_date = '2007-Apr-16'
go
set statistics profile off
go
1290674 1 SELECT * FROM [data_DTB] WHERE [report_date]=@1
1290674 1 |--Concatenation
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Mar 31 2007 12:00AM' AND Convert([@1])>='Jan 1 2007 12:00AM')))
0 0 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q1].[idc_be_data_DTB_part_2007_q1_report_date_param]), SEEK:([be_data_DTB_part_2007_q1].[report_date]=Convert([@1])) ORDERED FORWARD)
1290674 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Jun 30 2007 12:00AM' AND Convert([@1])>='Apr 1 2007 12:00AM')))
1290674 1 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q2].[idc_be_data_DTB_part_2007_q2_report_date_param]), SEEK:([be_data_DTB_part_2007_q2].[report_date]=Convert([@1])) ORDERED FORWARD)
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Sep 30 2007 12:00AM' AND Convert([@1])>='Jul 1 2007 12:00AM')))
0 0 | |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q3].[idc_be_data_DTB_part_2007_q3_report_date_param]), SEEK:([be_data_DTB_part_2007_q3].[report_date]=Convert([@1])) ORDERED FORWARD)
0 1 |--Filter(WHERE:(STARTUP EXPR(Convert([@1])<='Dec 31 2007 12:00AM' AND Convert([@1])>='Oct 1 2007 12:00AM')))
0 0 |--Clustered Index Seek(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q4].[idc_be_data_DTB_part_2007_q4_report_date_param]), SEEK:([be_data_DTB_part_2007_q4].[report_date]=Convert([@1])) ORDERED FORWARD)
As far as i see it checks filter parameter fitting CHECK constraint for each partition. Then it peforms clustered index seek for partition actually containing data and avoids using 3 other partitions. Thats great! This example just illustraits that partitioning actually works for me.
Unfortunately, there is another query with just awful performance on partitions comparing to single table. Lets try to get few rows entered last day:
Code Block
set showplan_text on
go
select top 10 * from DTB order by report_date desc
go
set showplan_text off
go
|--Top(10)
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([MYDB].[dbo].[DTB]))
|--Index Scan(OBJECT:([MYDB].[dbo].[DTB].[IX_DTB_DT_ACC]), ORDERED BACKWARD)
Excellent! It runs just for only few seconds. But using partitions:
Code Block
set showplan_text on
go
select top 10 * from data_DTB order by report_date
go
set showplan_text off
go
|--Top(10)
|--Merge Join(Concatenation)
|--Merge Join(Concatenation)
| |--Merge Join(Concatenation)
| | |--Sort(ORDER BY:([be_data_ DTB_part_2007_q1].[report_date] ASC, [be_data_ DTB_part_2007_q1].[param] ASC, [be_data_DTB_part_2007_q1].[param_value1] ASC, [be_data_ DTB_part_2007_q1].[param_value2] ASC, [be_
| | | |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q1].[idc_be_data_DTB_part_2007_q1_report_date_param]))
| | |--Sort(ORDER BY:([be_data_DTB_part_2007_q2].[report_date] ASC, [be_data_ DTB_part_2007_q2].[param] ASC, [be_data_DTB_part_2007_q2].[prama_value1] ASC, [be_data_DTB_part_2007_q2].[param_value2] ASC, [be_
| | |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q2].[idc_be_data_DTB_part_2007_q2_report_date_param]))
| |--Sort(ORDER BY:([be_data_DTB_part_2007_q3].[report_date] ASC, [be_data_DTB_part_2007_q3].[param] ASC, [be_data_DTB_part_2007_q3].[param_value1] ASC, [be_data_DTB_part_2007_q3].[param_value2] ASC, [be_data_
| |--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q3].[idc_be_data_DTB_part_2007_q3_report_date_param]))
|--Sort(ORDER BY:([be_data_DTB_part_2007_q4].[report_date] ASC, [be_data_DTB_part_2007_q4].[param] ASC, [be_data_DTB_part_2007_q4].[param_value1] ASC, [be_data_DTB_part_2007_q4].[param_value2] ASC, [be_data_
|--Clustered Index Scan(OBJECT:([MYDB].[dbo].[be_data_DTB_part_2007_q4].[idc_be_data_DTB_part_2007_q4_report_date_param]))
As one can see that€™s just awful . When I make graphical execution plan with Ctrl+L it says costs for Sort operations are thousands. I didn€™t run this query to check statistics profile, because on our server it will run for hours.
I found a topic regarding this problem: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1270479&SiteID=17
Mostafa Elhemali describes exactly my problem in the last post. I also though that getting top 10 * from partitioned view shouldn€™t be a problem €“ it€™s quite obvious just to grab top 10 from each partition and then find top 10 amongst them. Looks like it doesn€™t work this way though.
So the question is. Is there any new workarounds for this problem? Or maybe it is already solved in latest patches for SQL2005? I know that SQL2005 introduces new way of partitioning tables, maybe the problem will go if using SQL2005 partitioned tables instead of oldstyle partitioned views?
Thank you.
p.s. Upon reviewing my post i noticed that issued ORDER BY report_date DESC against unpartitioned table, and ORDER BY report_date against partitioned view. Well, specifying ORDER BY report_date DESC for partitioned view gives similar results, except for few ASCs are replaced with DESCs.
View 2 Replies
View Related
Feb 14, 2008
This query demonstrates a problem I have run across:
USE AdventureWorks
GO
-- This query works fine.
SELECT DISTINCT FirstName AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query also works fine.
SELECT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
-- This query returns error 145
SELECT DISTINCT ISNULL(FirstName, '') AS Name1 FROM Person.Contact ORDER BY FirstName
GO
The last query returns the error "ORDER BY items must appear in the select list if SELECT DISTINCT is specified".
It will work if I change ORDER BY to use "Name1" instead of "FirstName", but in the situation I have at hand, the query is generated by third-party software and I don't have the ability to change it.
Can anyone explain why what's going on here? Oddly, this same query will work if I run it against SQL Server 2000.
View 8 Replies
View Related
