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?
insert into test_sort values('Non-A'); insert into test_sort values('Non-O'); insert into test_sort values('Noni'); insert into test_sort values('Nons');
then execute the following selects: select * from test_sort order by cast( 1 as nvarchar(75));
select * from test_sort order by cast( description as nvarchar(75));
I have a DTS package that exports table from SQL Server to an Oracle database. However, I want to change the order in which the tables get exported because of referential integrity. I can find no way in the DTS designer to do that! IS there a way to change this order other than specifying a querY for each and every table!
The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?
I am having difficulty constructing a where clause. I have 4 columns in a table. Col1 is never NULL. Col2,3,4 may or may not contain NULL values. Here is the situation.
1. Col2,3,4 are NULL WHERE Col1 = condition
2. Col2 is NULL, 3 may or may not be NULL. When Col3 is NOT NULL, Col4 may or may not be NULL
where col1 and (col3(Not Null values) OR Col4(Not Null values)) = condition.
For a variety of reasons we need to change the default sort order in a SQL7 installation on about 25 servers. It's currently case insensitive, but we need to change it to case sensitive. To do this we propose to deinstall and reinstall SQL. In order to preserve the data in the tables can anyone suggest the correct procedure.
This subject has been posted several times, but I haven't seen a goodanswer.Problem:I want to change the order of the columns in a table using T-SQL only.Explanation:After running your code, I want to see the following table...CREATE TABLE [dbo].[TableName] ([First_Column] [int] NULL ,[Second_Column] [varchar] (20) NULL) ON [PRIMARY]look like this...CREATE TABLE [dbo].[TableName] ([Second_Column] [varchar] (20) NULL ,[First_Column] [int] NULL) ON [PRIMARY]Limitations:Don't post if your post would fall in the following categories:1. If you don't think it can be done2. If you think Enterprise Manager is the only way to do this3. If you think I should just change the order of my Selectstatements4. If you want to state that order column doesn't matter in arelational database5. If you want to ask me why I want to do thisWish:Hopefully the answer WON'T involve creating a brand new table, movingthe data from old to new, dropping the old table, then renaming thenew table to the old name. Yes, I can do that. The table I'm workingwith is extremely huge -- I don't want to do the data juggling.Thanks in advance!
Hello,Ive got a column which stores integers ranging from 0-200. I need to order them so that 1 is first, and 0 is last like 1,2,2,3,4,6,8....98....0,0,0My Order By clause statement looks like 'ORDER BY column_name', but obviously this will put the '0' records at the top. Is there a way around this?Thanks, Curt.
The following SELECT query gives me a list of 50 plus countries. How do I order them by 'United States' First (happens to be ID 225) and then alphabetical? SELECT Country_ID, Country_Long FROM Countries WHERE isIndustrial = 1 ORDER BY Country_Long
Hi! I think the order by clause is driving me crazy.The following T-SQL query works: SELECT count(*) AS c From F_POST Where id=@id Order by c Ok, so far so good, but in the following case it is NOT possible to order the result set according to "count(*)": Select T_Date AS TDATE, count(*) AS c From F_Post Where id=@id Order By case when @OrderBy = 1 then c elseT_DATE DESC This is just strange since it is essentially the same query!? Furthermore, it seems to be inpossible to have a order-by-clause that looks like: Order By case when @OrderBy = 1 then T_Column1 ASC elseT_Column2 DESC
Does anyone know how I can implement querys that do (almost) exactly this what the last 2 querys "should" to do?
like so often my Forums database design (in its simplest form) is:Forums -ForumID -Title -CategoryForumsMsgs -fmID -DateIn -AuthorID -MessageI need to create a sql query which returns all forum titles along with some data for 1) the first message entry (date created and author) and 2) the last one. So how can I do a JOIN query which joins with a ORDER BY clause so that the top/bottom entry only is joined from the messages table?
When I say to sort on a datetime field on descending order, the date is sorted. However, the time difference is not reflected in the results. Any way, we can fix it. i.e. If I have two records with the same dates but different times, the sorting order is not considering the time.
I am trying to pass as an input parameter a user selected order by clause, and instead of repeating the SQL statement with a new Order By based on the parameter, I want to set the Order by using this parameter. I can't get it to work.
IF @StartDate = 'ALL DATES' BEGIN SELECT @MinDate = Min(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MinDate = @StartDate END
IF @EndDate = 'ALL DATES' BEGIN SELECT @MaxDate = Max(AccessTime) FROM tblAudit END ELSE BEGIN SELECT @MaxDate = @StartDate END
BEGIN SELECT tblReports.ReportName, tblReports.ReportCode, tblAudit.BadAttempts, tblAudit.LogonUser, tblAudit.AccessTime, tblAudit.RemoteHost, tblAudit.RemoteIdent, tblAudit.ExitTime, tblAudit.BrowserType, tblAudit.Access_ID, TotalTime=DateDiff(Minute,tblAudit.AccessTime,tblA udit.ExitTime) FROM tblReports INNER JOIN tblReportsAccess ON tblReports.Report_ID = tblReportsAccess.Report_ID INNER JOIN tblAudit ON tblReportsAccess.Audit_ID = tblAudit.Audit_ID WHERE tblAudit.AccessTime >= @MinDate AND tblAudit.AccessTime <= @MaxDate ORDER BY (SELECT 'ColumnName'=ColumnName FROM tblOrderBy WHERE ColumnName = @OrderBy) END
I have a query that returns several ordered rows where one of the fields in the ORDER BY clause is a date field (DueDate) that we use to see the most pressing deadline first. The problem is that the default value in that field (which other code translates to mean no due date) is 1/1/1900. That means that items with no due date show up before today's import deadline. I can see one potential solution where I join my results on the original table where DueDate>1/1/1900 and then back to my results so I can use an ISNULL() on the field to set a value in the future (like 1/1/9999), but that seems like a really nasty wrong round-about way to do it. I think there has to be something better.
I have a a grid (Fig-1) where i have LineID and corresponding RankValue. I want to sort out the Grid like (Fig-2) where It will be sorted based on Rank Value(Higher to lower) but LineID group should maintain. I am using SqlServer 2008.
Need to pass column to ORDER BY as parameter in sp (possible 8 columns out of total 30). Is there a way to do it avoiding dynamic SQL use(will be used frequently)?
I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.
The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):
QUERY 1
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=? and s.ldate<=? and v.mdtid=20411
order by s.ldate desc
RESULTS 1
DATE MDT IDPU Odometer DO Odometer Total Miles 12/6/2007 2041112810.6 12874.5 63.9
QUERY 2 (with Union)
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=[From Date,Date] and s.ldate<=[To Date,Date] and v.mdtid=20411
Union
select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s ON ES.SchId=S.SchId JOIN vehicles v ON v.vehicleid=es.vehicleid JOIN Events PO ON PO.schid=es.schid AND PO.EvStrId=ES.EvStrId AND po.activity=4 JOIN Events PI ON PI.schid=es.schid AND PI.EvStrId=ES.EvStrId AND pi.activity=3
WHERE es.providerid in (0,1,4) and s.ldate>=? and s.ldate<=? and v.mdtid=2642
order by s.ldate desc
RESULTS 2
DATE MDT ID PU OdometerDO Odometer Total Miles 4/10/2007 20411 1207.21252.5 45.3 1/2/2007 2642 193652.6193817 164.4
As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
Hi, Im using a select query in which im using order by clause on a column which is varchar. Im getting wrong result on using the query, the result output is below
For my reports I have a Sort By parameter which has 2 values - Customer Name & Customer Number. for my dataset I have added @SortBy as parameter and assigned the value = Parameter!SortBy.value.
In the query I want to set the Order By clause based on the user selection. eg.:
select * from dbo.customers where name = @CustomerName order by @SortBy
However, I am unable to do this. I always get an exception for the order by clause no mater what. I have also tried the following queries in the query designer for the dataset customers but none of them work
="select * from dbo.customers where name " + @CustomerName + " order by " + @SortBy
select * from dbo.customers where name = @CustomerName order by + @SortBy
I know that I can set the interactive sort on the column headers and the interactive sort works, but the customer wants to have the ability to set the Sort By using the dropdown list.
First let me say, I really can't believe this chain of events myself--and they are happening to me.
I am upgrading several DTS packages to SSIS on what will be my new production server. These packages create tables, export them to a flat file, and ftp them off to other locations.
What is happening (on the SSIS side) is that the OLE DB Source is reordering some of the columns on its own (moving them to the end of the table/file. Then when my pickup/load routines run, the data is out of place and they fail.
Can anyone please explain what is happening here with the mapping. I have evaluated the table and the columns are in the order that I expect. When I preview the source table in the OLE DB Source Editor the columns are in the correct order/alignment, but when them in the OLE DB Source Editor --Columns section within BIDS the order is changed arbitrarily.
I have been somewhat successful (2 out of 3) in being able to re-map the data, but this last table just doesn't want to change.
Thanks in advance for any help and/or information you can provide
I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want. I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code. Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables. Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order. When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order. Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly? Regards, Robert Barnes
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
Hi,I'm trying to remove certain words from my Order clause. For example on iTunes they have removed the word 'The' from the start of artist names so that all the bands that start with 'The' don't appear grouped together. I'm trying to do a similar thing with University names so that all universities which begin with 'University of' or 'The' don't appear together.Is this possible?Thanks for your help!,Curt.
Hi AllI am having a problem with an ORDER BY clause when selecting information from multiple tables. EgSELECT i.InvoiceId, pd.PayDescription, u.UserNameFROM Invoice i LEFT OUTER JOIN tblPay ON i.PayId = pd.PayId LEFT OUTER JOIN tblUsers ON i.UserId = u.UserIdORDER BY pd.PayDescriptionthis is just an example my query is a lot more complex. Is there any simply way you can do an order by in this way?I am writing this for MSSQL Server 2000ThanksBraiden
Hi, A quick background on the problem; My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place. I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance. Except.... None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio). So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT... If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code. If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever. To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED. I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days! Can anyone help?
