Select Parameter Order

Jul 20, 2005

Which is more efficient

Where NonindexedColumn=x and IndexedColumn=y
or
Where IndexedColumn=y and NonindexedColumn=x

or does matter? Will the optimiser work it out?

(I'm building the SQL string on the fly in the fron-end)

View 1 Replies


ADVERTISEMENT

Default Sort Order - Open Table - Select Without Order By

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

Specify Order For Select Results, Order By: Help!

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

INSERT-SELECT Depending On The Select:ed Order

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

Order By Parameter

Mar 12, 2004

Is it possible to use a variable like @SelectOrderBy with the Order By of a stored procedure? or do I need several SP's?

Select *
FROM Table
Order By @SelectOrderBy

I want to let a user select from a drop down on a webform the order by column to use.

Thanks

View 2 Replies View Related

Order Parameter

Oct 26, 2006

why does this order parameter not work?!

Declare @SortOrder varchar(50)

ORDER BY CASE
WHEN @SortOrder = 'Sales' THEN Sum(SALES.Sale)
WHEN @SortOrder = 'Profit' THEN Sum(GP)
WHEN @SortOrder = 'Product' THEN SALES.Product
END


Error:

Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 8
Error converting data type varchar to float.

View 6 Replies View Related

Pass A Parameter For Sort Order

Jan 4, 2005

Is there a way to pass a parameter into a stored procedure to set the sort order.

I have tried things like

ORDER by EmployeeID @direction

where @direction is a varchar with a value of 'desc' and I get an error.

I do not want to dynamically create the sql and use sp_executesql.
I have already done it this was but I am looking for a better solution.

View 1 Replies View Related

Passing ORDER BY Parameter Causes Error

Feb 7, 2005

Hi everyone,

I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:

CREATE PROCEDURE GetFromTable
(
@SortOn varchar(20)
)
AS
SELECT *
FROM Table
ORDER BY @SortOn
GO

This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?

Thanks in advance,

Piet

View 9 Replies View Related

SQL Server 2012 :: How To Order By Using Parameter Value

Jul 15, 2014

I have a stored procedure that I would like to order by the order of the parameters it takes in starting for current item number 1, prior item number 1, current item number 2, prior item number 2, and so on.

currently I am ordering it by:

c.CurrentItemNumber
, p.PriorItemNumber

among other fields, but I would like to replace the part above with the parameters 1 through 5 (current and prior).

Is this possible?

This is my stored procedure for reference:

ALTER PROCEDURE [cost].[Ingredient_Cost_Comparison]
(
@CurrentSalesQuoteNumberNVARCHAR(20)
,@PriorSalesQuoteNumberNVARCHAR(20)
,@CurrentItemNumber1NVARCHAR(20)
,@PriorItemNumber1NVARCHAR(20)
,@CurrentItemNumber2NVARCHAR(20)

[Code] ....

View 5 Replies View Related

Stored Procs - Specifying ORDER BY As A Parameter

Jul 23, 2005

hi all, hope someone can help....i'm having trouble calling an SP where the ORDER BY operator is specified asa parameter when the SP is calledmy SP is.....CREATE PROCEDURE dbo.sp_CustSearch (@SearchFor VARCHAR(80) , @SortOrderVARCHAR(50))ASBEGINSELECT first_name, last_name, postcode , address, townFROM customer WITH (nolock)WHERE (UPPER (last_name) LIKE '%' + @SearchFor + '%'OR UPPER(address.postcode) = @SearchFor )ORDER BY @SortOrderENDGOthe line causing the problem is ORDER BY @SortOrderthe error i get on checking the syntax is:"Error 1008: The SELECT item identified by the ORDER BY number 1 contains avariable as part of the expression identifying a column position. Variablesare only allowed when ordering by an expression referencing a column name"anyone know how to solve this? i'm guessing it's something simple.enclosing @SortOrder in single quotes gives, unsuprisinlgy, unsortedresults.trying ORDER BY '[' + @SortOrder + ']' gives the same error asabovethe only way i've managed to get it working so far is to dynamically buildthe SQL statement and then execute that at the end. it's a little messywhich was why i was trying to get the above working.thanks in advance.

View 4 Replies View Related

How To Set Default Parameter To Select All For A Multivalue Parameter

Jul 24, 2007

I have a dataset listing distinct values for items (like 1, D10, M4, etc.) The WHERE statement in my query refers to unit IN(@Unit). I then have 2 report parameters to select 1) a date (datetime); and 2) a multivalue parameter to select one or all of the "units". I would like the second parameter to default to "Select All". Can someone tell me how to do this? I'm sure this is a fairly simple thing but I am really struggling. The report parameter is set as multivalue; My "available values" is set to "from query" and refers to my "unit" dataset and the value and label fields are set to "unit" (only field I bring into this particular dataset). The "Default Values" section is set to "from query" , the dataset is set to "unit" and the value is set to "unit". I can preview the report and select a date but the list of units comes up with all boxes unchecked, including "Select All". Any help will be much appreciated. Thanks.

View 11 Replies View Related

SqlDataSource SelectCommand, Parameter Order Matters?

Feb 24, 2008

Hi all,
I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run.  In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run.  I've finally narrowed down the issue to something that resolves the symptoms to something in my query;
SELECT     f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical,                       SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total,                       SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM         t LEFT OUTER JOIN                      d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN                      tblFac AS f ON t.sUnitID = f.UnitID_1WHERE     (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division
If I change my WHERE clause to the following
WHERE     (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))
performance is MUCH better.  How could such a little change make such a big difference in speed?
Thanks.
-e

View 3 Replies View Related

How To Pass Dynamic Parameter To Order BY Clause

Sep 13, 2004

Hi,

I use a DataGrid to show the data, and I want it has a sorting and Paging function,
so I use dataset to collect the data from middle tier function and stored procedure.
I have code in aspx page like BindData(ViewState("SortExpr")).

In the stored procedure I pass SortExpr as parameter as following:

CREATE Procedure Ruying_AutoSearch10
(
@Make varchar(50),
@Model varchar(50) = NULL,
@Condition varchar(20) = NULL,
@Miles float,
@Zipcode varchar(5),
@SortExpr varchar(100)
)
AS

DECLARE @RowCount int
SELECT @RowCount = Count(*) FROM ZIPCodes WHERE ZIPCode = @Zipcode AND CityType = 'D'

if @RowCount > 0
BEGIN
SELECT
z.ZIPCode, z.City, z.StateCode, a.Make, a.Model, a.AutoPrice, a.AutoPrice2, a.AutoYear, a.Mileage, a.AdID, a.ImageURL, dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) As Distance
/*
The above functions requires the Distance Assistant.
*/
FROM
ZIPCodes z, RadiusAssistant(@ZIPCode,@Miles) r, AutoAd a
WHERE
z.Latitude <= r.MaxLat
AND z.Latitude >= r.MinLat
AND z.Longitude <= r.MaxLong
AND z.Longitude >= r.MinLong
AND z.CityType = 'D'
AND z.ZIPCodeType <> 'M'
AND z.ZIPCode = a.Zipcode
AND a.AdActive = '1'
AND a.AdExpiredate >= getdate()
AND a.Make = @Make
AND a.Model = IsNull(@Model,a.Model)
AND a.Condition = IsNull(@Condition, a.Condition)
AND dbo.DistanceAssistant(z.Latitude,z.Longitude,r.Latitude,r.Longitude) <= @Miles
ORDER BY @SortExpr
END
ELSE
SELECT -1 As ZIPCode
--ZIP Code not found...
GO


but I got the error as "variables are only allowed when ordering by an expression referenceing
a column name". How I fix this error? Please help.

Thanks.

Lin

View 9 Replies View Related

Sort Order Via Parameter-now Security Issue

Mar 19, 2007

we are firing this from my asp page, what i can't work out how to do is pass a sort order via parameter to my stored procedure.
Any Help appreciated

Nigel

set conn = CreateObject("ADODB.Connection")

conn.open MM_fc_conn_STRING

sql = "EXEC Sproc_01020_Select_WebUser" & " @UserLoginName='" & Request.Form("Username") & "'"

set Recordset1 = conn.execute(sql)

View 17 Replies View Related

Reporting Services :: Parameter Order Of Precedence

Jul 1, 2015

I've run into something that really isn't a problem - I corrected the issue with a CTE solution - but, it did raise a question.

I was developing a report and, when I previewed it, I got an error that a parameter was dependent upon the value of another parameter - no forward dependencies allowed.

But, another parameter that used the exact same SQL code - just different fields - works fine.

So, here's the list of parameters;

CurDate is derived by;
DECLARE @CurDate AS date
SET @CurDate =
(
SELECT MAX(Load_Date)
FROM Census_Rpt_Final
)

The parameter that gives me the problem is PCPName. It is a list of names derived by the following;

SELECT DISTINCT
UPPER(PCP_Name) AS [PCP Name]
FROM
Census_Rpt_Final
WHERE
Load_Date = @CurDate
ORDER BY
[PCP Name];

So, OK, fine, I can understand the error. But, MemberName is derived by;

SELECT DISTINCT
RTRIM(UPPER(Member_LName))
+ ', '
+ RTRIM(UPPER(Member_Fname)) AS [Member Name]
FROM
Census_Rpt_Final
WHERE
Load_Date = @CurDate
ORDER BY
[Member Name];

And, it works fine. No Foward Dependency problem at all.

So, my question becomes one of precedence. I see, in the list of parameters, that PCPName precedes CurDate which precedes MemberName. Is the problem because, when it hits PCPName, CurDate has yet to be defined, but when it hits MemberName, CurDate has been defined?

Is SSRS one-pass compilerĀ or multi-pass? My guess is that it's a one-pass, because of the error I'm getting. As I said, I've corrected the problem using;

WITH Date_of_Most_Recent_Census AS
(
SELECT
MAX(Load_Date) AS [CurDate]

[Code] ....

Removing the parameter reference from the code, but it made me curious as to why one worked and the other didn't.

View 2 Replies View Related

Remove Select All Options From Multi Select Parameter Dropdown

Jun 8, 2007

Hi All



I am using SQL Server 2005 with SP2. I have multi select parameter in the report. In SP2 reporting services gives Select All option in the drop down.



Is there any way I can remove that option from the list?



Thanks

View 4 Replies View Related

Passing Table Name And Order By Parameter To Stored Procedure

Jul 27, 2007

can i pass the name of the table and the "order by" column name to stored procedure?
 i tried the simple way
(@tablename varchar and then "select * from @tablename)
but i get error massesges. the same for order by...
what is the right syntex for this task?

View 2 Replies View Related

SQL Server 2012 :: How To Modify ORDER BY Clause At Runtime Using Parameter

Jun 4, 2015

the code below works (this is only a quick dumbed down version of the actual code, it might not work 100% for all cases). Is it at all possible to exploit the functions that were added to SSQL since v. 2005 to simplify this code ?

In SSRS, a parameter allows the user to create a list of invoices (from CRM) to be ordered in any of the following ways the user prefers:

'Document Date (most recent date first)'
'Document Number (highest number first)'
'Document Date (most recent first) and Number'
'Document Number (lowest number first)'

The invoices have a (supposedly) sequential identity-generated number. However Accounting may want to set a different date than the creation date on some invoices. So there is no way the invoice numbers will be in the same sequence as the invoice dates.

So I just created the "sorting fields" - they appear as junk in the output dataset (just do not drop them in the SSRS tablix - they have to be part of the SELECT statement to be usable in the ORDER BY clause.

The code is:

DECLARE @ls_OrderBy varchar(80)
--'Document Number (highest number first)'
--'Customer and Document Date (most recent date first)'
--'Customer and Document Number (highest number first)'
--'Document Date (most recent first) and Number'

[code]....

View 9 Replies View Related

Parameter: C, I, P, Or ALL...how To Select Based On Parameter?

Jul 27, 2006

Hi everyone,

I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...

Any thoughts??

Query:


sql Code:






Original
- sql Code





ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS

SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName

FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID

INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID

INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID

INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID

Where @status = A3.tStatus ...






 ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]    (        @status varchar(25),        @course varchar(100),        @date   smalldatetime    )AS   SELECT    A1.uLastName,    A1.uFirstName,     A2.mName,    A3.tStatus,    A3.tScore,    A3.tStartDate,    A3.tCompleteDate,    A4.cName     FROM VSALCP.dbo.[User] AS A1    INNER JOIN FSDLMS.dbo.Student AS A5    ON A1.uID = A5.stID     INNER JOIN FSDLMS.dbo.Transcript AS A3    ON A3.t_FK_stID = A5.stID     INNER JOIN VSALCP.dbo.Member AS A2    ON A2.mID = A1.u_FK_mID      INNER JOIN FSDLMS.dbo.Course AS A4    ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...


"If status = ALL select * status"

Thanks for taking a look!

View 3 Replies View Related

Transact SQL :: Retrieve Return Result With Chronological Order Based On Parameter

Jun 23, 2015

Goal: My request is the retrieve the return result from sp_Test as 8, 2, 4, 1 ,3 (take a look at picture 1) based on the chronological list from User-Defined Table Type dbo.tvf_id.

Problem: When I execute the stored procedure I sp_Test I retrive the list that is from 1 to 8. I don't know how to do it?

Information: I'm using SQL server 2012

create table datatable (id int,
name varchar(100),
email varchar(10),
phone varchar(10),
cellphone varchar(10),
none varchar(10)
);
insert into datatable values

[Code] .....

View 2 Replies View Related

Multi Select Parameter From Function - Select All?

Sep 6, 2007



I am using RS 2000. I have a multi select parameter where I can select multiple states by separating with a comma. I am trying to figure out how to incorporate an "All" parameter.

Query:

Select [name], city, state, zipcode
From Golf inner join charlist_to_table(@State,Default)f on State = f.str

Function:

CREATE FUNCTION charlist_to_table
(@list ntext,
@delimiter nchar(1) = N',')
RETURNS @tbl Table (listpos int IDENTITY(1, 1) NOT NULL,
str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @pos int,
@textpos int,
@chunklen smallint,
@tmpstr nvarchar(4000),
@leftover nvarchar(4000),
@tmpval nvarchar(4000)
SET @textpos = 1
SET @leftover = ''
WHILE @textpos <= datalength(@list) / 2
BEGIN
SET @chunklen = 4000 - datalength(@leftover) / 2
SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
SET @textpos = @textpos + @chunklen
SET @pos = charindex(@delimiter, @tmpstr)
WHILE @pos > 0
BEGIN
SET @tmpval = ltrim(rtrim(left(@tmpstr, @pos - 1)))
INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
SET @pos = charindex(@delimiter, @tmpstr)
END
SET @leftover = @tmpstr
END
INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)),
ltrim(rtrim(@leftover)))
RETURN
END
GO


Anyone have any ideas?

Thanks,
Deb

View 5 Replies View Related

Select Top And Order By

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

Order By With Select Into

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

UNION: Select Order

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

How Do I Use Order By When I Use Select Distinct.

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

SELECT DISTINCT W/ORDER BY

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

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 View Related

Select Top Alters Order

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

Order By In A INSERT INTO..SELECT

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

Tricky SELECT And ORDER BY

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

Select Like And Order Results

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

INSERT INTO As SELECT With ORDER BY

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

Reporting Services :: Change Order Of The Day Of Week Names In Parameter Drop Down List In SSRS?

Aug 26, 2015

I have a requirement to show Day of week in parameter drop down list in different order, actual order is Monday to Sunday (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday) in DayOfWeek dimension in cube.

But my requirement is to show Friday to Thursday(Friday,Saturday,Sunday,Monday,Tuesday,Wednesday,Thursday) in DayOf Week parameter drop down list and report table. How I can get this requirement done.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved