I have a table:
CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]
SELECT *
FROM dbo.tx1
ORDER BY seq DESC
go
f1 seq
zz 1003
uu 1002
kk 1001
yy 1000
create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1
yy 1000
kk 1001
uu 1002
zz 1003
This works fine in SQL 2000, but not in SQL 2005
View 12 Replies View RelatedHi all.
In one table fields vales are 9, 5, 7, 2, 5, 6, 44, 67, 789.
I want to get 5 high values so please can you write sql query for this question.
Thanks.
Zahyea.
Hi,
I have a table in which there is 5 column ...one with numbers like...1,2,3,4,..20...and one column is with description....and other column with other details...
Now I want to disply my results in sequence followed by 1,2,3,4,5,17,18,15,16,10,11,20 with all other columns...so can anybody suggest me what to do..?
Hi fellows
I have these values with a PK and length.
PK length
dd1 2
dd1 4
dd1 6
dd1 8
dd1 9
dd2 1
dd2 3
dd2 6
dd2 9
dd3 3
dd3 4
dd3 9
dd3 15
dd3 25
I want to create another column showing a sequency like this THIS MEANS THAT WHEN I CHANGE THE PK I HAVE TO START WITH CERO.
PK FROM TO
dd1 0 2
dd1 2 4
dd1 4 6
dd1 6 8
dd1 8 9
dd2 0 1
dd2 1 3
dd2 3 6
dd2 6 9
dd3 0 3
dd3 3 4
dd3 4 9
dd3 9 15
dd3 15 25
CHEERS
I have the following data in a table:
Account SEQ
12345 1
12345 2
12345 4
12345 5
12345 7
I need to fix the SEQ field so that no gaps exist, like this:
Account SEQ
12345 1
12345 2
12345 3
12345 4
12345 5
Is there a way to do this with T-SQL?
I need to know the steps on how to configure the SSRS Forms Authentication and its IIS Configuration. What specific Config Files should be modified?
i am using IIS 6.0, Windows Server 2003, SQL Server 2005.
Please help....
Hi,
I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:
ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT
PP_PERIOD_ID,
CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC
The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help
- T.A.
In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?
DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR
I have created view by jaoining two table and have order by clause.
The sql generated is as follows
SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth
If I run sql the results are displayed in proper order but the view only order by first item in order by clause.
Has somebody experience same thing? How to fix this issue?
Thanks,
I am using Sequence Clustering algorithm. (I've built several models with Clustering algorithm and Decision Trees for this client, which work fine.).
Background: Sequence data must be stored in a nested table, which can have only 1 non-key attribute.
I specify a mining model structure with the nested table key as the datetime, and the nested table discrete prediction column as [sort name] . this builds the model fine.
When I try to process this data mining model, I get Process failed: "Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect".
Iit may be that OLAP distinct count requests numerical data type, but not from the examples I've seen. Tried this anyway €“ doesn€™t work on numeric either €“ same problem.
Any Suggestions?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
View 13 Replies View Relatedsomebody help me??
View 4 Replies View RelatedI have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT
SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
Thanks,
Kevin
I have a table like this
create table
#invoice (invoice_no varchar(5), invoice_amount int)
INSERT INTO #invoice
SELECT 'A', 100 UNION
SELECT 'A', 200 UNION
SELECT 'A', 300 UNION
SELECT 'B', 400 UNION
SELECT 'B', 500 UNION
SELECT 'B', 600 UNION
SELECT 'B', 700
Now I want a output like this
INVOICE_NO AMOUNT SEQUENCE_NO
A 100 1
A 200 2
A 300 3
B 400 1
B 500 2
B 600 3
B 700 4
The sequence_no should set back to 1 on change of invoice_no
these are items in a an invoice. they just want a sequence_no
I can do this using cursors, but if anyone can help with WHILE LOOP
would be great
THANKS
-----------------------------------------------------------------------------------------------
Ashley Rhodes
I have a view in SQL SERVER 2005 which includes ORDER BY in my SQL expression.
When I press "execute", the results of the view come out ordered by the selected field, as they should be.
When I close the view and choose "Open View", the results come out not ordered. The same thing happens when I'm trying to show the results in a GridView.
Why does it happen?
I have to provide the Order by Value from Front End (VB) to the DB , so that I can receive the Crystal Report in the order that I have given.
thanks in advance... :confused:
In MSSql,"Order by" and "distinct" can be used in view?
thanks!
i m required to use order by in a view, how to do it, simple order by doesn't work for it, any other way,i have heard there is one in oracle, but wat abt SQL Server???:confused:
View 2 Replies View RelatedI have make a view where i have a order by
when i execute my view in modify my result are allright
but when i say open view to see my view it is not
in order by.
I use the view in a asp site to the internet and i can't use order by here
because the field i orderby shall not come up in my site, i only use this field
to order by
Hope someone can help
Alvin
I have downloaded and installed SQL Server Express 2003 Adv.. I have created a new database in it and also a table called Customers. In that I have created one field called CustomerName as nvar(255). I have made 10 records by filling in the field with various names. I have then created a new view and asked it to order the records in descending order. When I save and show the view it shows the records still in the order that I inserted them not in descending alphabetical order. If I make it show in ascending order the same problem still occurs. Collation settings are correct for the computer at Latin1. However, I have tried a few others, but none make any difference. SQL Server Express 2003's view will not list my Customers in either ascending or decending alphabetical order. Can anyone give me any suggestions?
View 4 Replies View RelatedI have the view below and if I use vwRouteReference as the rowsourcefor a combo box in an MS Access form or run "SELECT * FROMvwRouteReference" in SQL Query Analyzer, the rows don't come throughsorted by Numb.Everything I've read on the web suggests that including the TOPdirective should enable ORDERY BY in views. Does someone have an ideawhy the sorting is not working correctly for this particular view? thanks.CREATE VIEW vwRouteReferenceASSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,tblItem.Numb, tblQuestion.DescrPrimary AS TypeFROM tblItem INNER JOIN tblQuestionON (tblItem.ID = tblQuestion.Item_ID)WHERE (((tblItem.Category_ID)>0))UNIONSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS TypeFROM tblItemWHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR(tblItem.Type) = 'Route'))ORDER BY tblItem.Numb
View 49 Replies View RelatedWhen I create a view in SQL and include an ORDER BY clause i can see itin Management Studio. However, when I call the same view from an ASPpage the order goes completely haywire.Any ideas?
View 24 Replies View Related
Hi,
I have created a view which uses 3 tables, i also have a sort on one of the columns. However when I open the view the sort does not work. It does however sort the view correctly when executing the query within design view
Can anyone explain this or is it a bug within SQL Server Express 2005?
thanks
David
Hello,
I am trying to create a simple view which uses self joins. I want the final result order by. I am able to create the view using Top clause followed by order by but when I simply query the view it does not appear in the correct order. Can some body help me with the issue.
CREATE VIEW [dbo].[vw_SalesRep_Chaining]
AS
SELECT Top 100 percent Rep_id AS RepId,
Rep_cd AS RepCode,
Region as Region,
CASE WHEN Market IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico') THEN 'Arizona / New Mexico'
WHEN Market IN ('L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'LA Metro'
WHEN Region = 'Western' AND Market NOT IN ('Arizona - North', 'Arizona - South', 'Gtr TX / New Mexico', 'L.A. Metro - West', 'L.A. Metro - North', 'L.A. Metro - East') THEN 'Western - Other'
ELSE '' END AS Sub_Region,
RSM AS RSM,
UPPER(Regional_Manager) AS Regional_Manager,
Market,
Rep_Manager_id AS SalesManager,
UPPER(MarketManager) AS MarketManager,
New_Position AS NewPosition,
Rep_Status AS RepStatus,
UPPER(Rep_Payroll_name) AS PayrollName
FROM (SELECT SalesRep_GUID, Rep_id, Rep_cd, Rep_Payroll_name, Rep_SSN, Rep_Status, Hire_dt, Termination_dt, Commission_start_dt,
Rep_Manager_id, Region, Market, New_Position, Validity_start_dt, Validity_end_dt, Load_interval_id, Create_process_id, current_ind
FROM dbo.SalesRep
WHERE (current_ind = 1) AND (New_Position IN ('SC1', 'SC2', 'SC3', 'MSO')) AND (Region NOT IN ('Emerging Market')) OR
(current_ind = 1) AND (New_Position IS NULL) AND (Region IN ('TeleSales')) AND (Region NOT IN ('Emerging Market'))) AS A
INNER JOIN (SELECT Rep_id RepId, Rep_Payroll_name as MarketManager, Rep_Manager_id as RSM
FROM dbo.SalesRep AS SalesRep_2
WHERE (current_ind = 1)) AS B
ON A.Rep_Manager_id = B.Repid
INNER JOIN (SELECT Rep_id RepId1, Rep_Payroll_name as Regional_Manager FROM dbo.SalesRep AS SalesRep_1
WHERE (current_ind = 1)) AS C
ON B.RSM = C.Repid1
WHERE (A.Region IS NOT NULL)
Order by Rep_Id, Rep_Cd
This is the script I have used to create the view. But when is simply query the view it does not appear in the order of Rep_Id, Rep_Cd
Hi there,
I have a view created using the following code. The view works perfectly but does not order by the name column as I've asked it to do. In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column.
Any help would be much appreciated. Here's the code:
SELECT TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name
FROM dbo.Members INNER JOIN
dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID
GROUP BY dbo.Members.DivisionID, dbo.Country.Name
ORDER BY dbo.Country.Name
And another, more simpler view, that doesn't sort as it's supposed to.
SELECT TOP (100) PERCENT CountryID, Name, RegionID, IsActive, HasFlag, URL, Comments
FROM dbo.Country
ORDER BY Name
Many thanks,
Ady
Hi,
I've a problem with a created view in sqlserver2000 that I'm now using in sqlserver2005.
This is the view :
CREATE VIEW hsarti01_VD1 AS
SELECT TOP 100 PERCENT *
FROM hsarti01 WITH(index(hsarti01_PK))
ORDER BY 1,2 desc,3,4
When I do the "select * from hsarti01_VD1" in sql server 2000, I see in the result that the order by is been using. but in sql server 2005 it's just using the order of the primary key and not the order by !
Has anyone have a solution for it ?
Thanks
I built a view that runs against the aspnet_WebEvent_Events table, part of the ASP.Net Health Monitoring.
Here is the view SQL:
SELECT TOP (100) PERCENT EventTime, EventType, EventSequence, EventOccurrence, EventCode, EventDetailCode, Message, ApplicationPath,
ApplicationVirtualPath, MachineName, RequestUrl, ExceptionType, Details
FROM dbo.aspnet_WebEvent_Events
ORDER BY EventTime DESC
What I found is that when the query is ran within the View Designer that the view is ordered by the correct EventTime (datetime) field.
But when the view is ran from right-clicking on the view and selecting "Open View" the order by does not take place.
Why is this happening and how do I fix it?
I'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
Hi,
I have few views in SQL Server 2005. In Design View, the results of View are ok. In OPEN VIEW option, records are not sorted correctly, ORDER BY is ignored.
What could be the reason for this ?
Thanks a lot in advance!
help with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc
TNX for help
How to use a dsn-less connection from DTS?Source: MySQLDestination: SQL Server 7.0I have the ADODB.connection code but unsure where and how to use it.Pls advise.TIA!Bob
View 1 Replies View RelatedCannot see the Colums in the "design view" of Queries. All i see when i want to design a new query is *columns
This happens in only one database, in other databases using same server i can see the colums and can tick them to view then in the query.
In enterprise manager i see all the columns.
Using SQL 2000 and MSAccess 2000