Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
select top 10 * from a order by aName Result is: 11Bank of abcde 10Bank of abcde 9Bank of abcde 8Bank of abcde 7Bank of abcde 6Bank of abcde 5Bank of abcde 4Bank of abcde 3Bank of abcde 2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
Hi, We got a problem. supposing we have a table like this:
CREATE TABLE a ( aId int IDENTITY(1,1) NOT NULL, aName string2 NOT NULL ) go ALTER TABLE a ADD CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId) go
insert into a values ('bank of abcde'); insert into a values ('bank of abcde'); ... ... (20 times)
select top 5 * from a order by aName Result is: 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
select top 10 * from a order by aName Result is: 11 Bank of abcde 10 Bank of abcde 9 Bank of abcde 8 Bank of abcde 7 Bank of abcde 6 Bank of abcde 5 Bank of abcde 4 Bank of abcde 3 Bank of abcde 2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot. So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
After a customer decides to buy a shopping list, there is generally aneed to store/insert one master record and a variable number of childdetail records, preferably all wrapped in a transaction. There are lotsof ways to do this and I am wondering if anyone knows which is mostefficient.One approach is to use ADO.NET's transaction capabilities, definesingle-record insert procs for the master and detail tables, and callthe detail insert in a loop from the web page. This has N+1 trips tothe server, which is not too attractive.Another approach is to concatenate all the data into a bigstring/varchar variable and pass it to a decoder proc that would thencall the single record insert procs via a loop inside the decoder proc.This second approach would use T-SQL's transaction capability and haveonly one trip to the server, but it is more effort to code on the webpage and in the decoder proc.Surely this is a common problem. Are there any more elegant/efficientmethods that anyone can suggest? Can one pass an array to a proc? Isthis a place for a user defined data type?Any advice is much appreciated.
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?
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NWHCConnectionString %>" SelectCommand="SELECT [Title], [URL], [Date] FROM [Article] ORDER BY [Date] DESC"></asp:SqlDataSource>
<asp:Repeater id="myRepeaterUL" runat="server" DataSourceID="SqlDataSource1"> <HeaderTemplate> <ul> </HeaderTemplate> <ItemTemplate> <li><a href="<%# DataBinder.Eval(Container.DataItem, "URL") %>"><%#DataBinder.Eval(Container.DataItem, "Title")%></a><br /><%# Eval("Date") %></li> </ItemTemplate> <FooterTemplate> </ul> </FooterTemplate> </asp:Repeater> This is my code above, I am trying to order them to show the four new list of news. Here is a picture, yeah its old and everybody loves pictures. see the box on the right, i want to show only four, not all of it.
need help how to change the shift order in my stord prosege backward on the field "shifttype" not like this shifttype --------------------------------------------------------- 111111 2008-02-24 Sunday 1 111111 2008-02-23 Saturday 2 111111 2008-02-22 Friday 3 111111 2008-02-21 Thursday 4 111111 2008-02-20 Wednesday 5 111111 2008-02-19 Tuesday 6 111111 2008-02-18 Monday 7 111111 2008-02-17 Sunday 8 111111 2008-02-16 Saturday 1 111111 2008-02-15 Friday 2 111111 2008-02-14 Thursday 3 111111 2008-02-13 Wednesday 4 111111 2008-02-12 Tuesday 5 111111 2008-02-11 Monday 6 111111 2008-02-10 Sunday 7 --------------------------------------------------------------------------------------- i need it like this shifttype ------------------------------------------------------ 111111 2008-02-24 Sunday 8 111111 2008-02-23 Saturday 7 111111 2008-02-22 Friday 6 111111 2008-02-21 Thursday 5 111111 2008-02-20 Wednesday 4 111111 2008-02-19 Tuesday 3 111111 2008-02-18 Monday 2 111111 2008-02-17 Sunday 1 111111 2008-02-16 Saturday 8 111111 2008-02-15 Friday 7 111111 2008-02-14 Thursday 6 111111 2008-02-13 Wednesday 5 111111 2008-02-12 Tuesday 4 111111 2008-02-11 Monday 3 111111 2008-02-10 Sunday 2
Code Snippet if object_ID('tempdb..#emplist','U')<>0 Drop Table #emplist if object_ID('tempdb..#empshifts','U')<>0 Drop Table #empshifts go declare @g datetime select @g=getdate() CREATE table #empList ( [empID] int NOT NULL, [ShiftType] int NULL, [StartDate] datetime NOT NULL, [EndDate] datetime NOT NULL ) INSERT INTO #empList ([empID], [ShiftType],[StartDate],[EndDate]) SELECT 111111,1,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103) UNION ALL SELECT 222222,2,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103)UNION ALL SELECT 333333,3,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL SELECT 444444,4,CONVERT(DATETIME, '01/01/2008', 103), CONVERT(DATETIME, '27/02/2009', 103)UNION ALL SELECT 555555,5,CONVERT(DATETIME, '01/01/2008', 103),CONVERT(DATETIME, '27/02/2009', 103) -- create shifts table CREATE table #empShifts ( [empID] numeric(18, 0) NOT NULL, [ShiftDate] datetime NOT NULL, [ShiftType] int NULL , [startingShiftType] int not null ) create unique clustered index uc_empshifts on #empshifts(empid,shiftdate DESC) declare @curr_employee int declare @shift_id int declare @dummyShift int declare @dummyEmp int --start by populating the dates into the @empshifts table insert #empshifts ( empid, shiftdate, [startingShiftType] ) select empid, dateadd(day,-1*spt.number,Enddate), shifttype from #empList cross join master..spt_values spt where spt.type='P' and spt.number<=datediff(day, startdate,enddate)
--now set up the shifts as the cursor solution did select @shift_id=0, @curr_employee=0 update e set @shift_ID=shiftType=(case when @curr_employee=empid then @shift_ID else startingShiftType end -1 + CASE WHEN @shift_id in ( 1,2,3) and DATENAME (dw,ShiftDate )='Friday' then 0 WHEN @shift_id= 8 and DATENAME (dw,ShiftDate )='Saturday' then 0 else 1 end)%8+1, @dummyshift=@shift_ID, @curr_employee =empid, @dummyemp=@curr_employee from #empshifts e WITH (index(uc_empshifts),TABLOCK) OPTION (MAXDOP 1) --show the results select empid,shiftdate, DATENAME (dw,ShiftDate ),shifttype from #empshifts --select datediff(ms,@g,getdate())
I want to get the list of items present in that order based on the confidentiality code of that product or Item and confidentiality code of the user.
I display the list of orders in first grid, by selecting the order in first grid I display the Items present in that order based on the confidentiality code of that item.
whenever order in 1st grid is selected i want to display the items that the item code should be less than or equal to the confidentiality code of the logged-in user other items should not display.
If the all the items present in the order having confidentiality code greater than Logged-in user at that time the order no# should not display in the first grid.
In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.
Any list of commands that require exclusive access in order for the command to complete? I had an instance today where a DBA executed sp_changedbowner command which is the alter database command on a production database and it locked it up.
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.
Hello,I am trying to follow along with the Data Access tutorial under the the "Learn->Videos" section of this website, however I am running into an error when I try to use the "Edit -> Update" function of the Details View form: I'll post the error below. Any clues on how to fix this? Thanks in advance!!! ~DerrickColumn name 'Assigned_To' appears more than once in the result column list. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Column name 'Assigned_To' appears more than once in the result column list.Source Error: Line 1444: } Line 1445: try { Line 1446: int returnValue = this.Adapter.UpdateCommand.ExecuteNonQuery(); Line 1447: return returnValue; Line 1448: }
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B red - pen red - pencil red - highlighter blue - marker blue - pencil green - pen green - highlighter red - pen blue - pencil blue - highlighter blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green pen 2 0 1 marker 0 1 0 pencil 1 3 0 highlighter 1 1 1
We tried Sum but not able to display in single row.
Does column order really matter for Query Optimizer to pick index.Case 1: Say my CUSTOMER table has one composite index containing FirstName and LastName. FirstName exists prior than LastName. Does the column, FirstName and LastName, order matter to have Query Optimizer to utilize the index when I write WHERE clause in a SELECT statement?Statement 1:SELECT * FROM CUSTOMER WHERE FirstName = 'John' and LastName ='Smith'Statement 2:SELECT * FROM CUSTOMER WHERE LastName ='Smith' and FirstName = 'John' Will both statement 1 and 2 use the composite index or only statement 1?Case 2:Say my CUSTOMER has two single-column indexes. One index is on column FirstName. Another is on column LastName.For statement 1 and 2 above, which index will be picked by Query Optimizer or both? How does QO pick for index?I read couple book and some books say column order matter but some say no. Which one should I go with? I'm kind of confused.
I'm using SQL Server 2005 and are having some troubble with sorting a paged result set. I'm using the OVER Clause to achieve the sorting and paging and have the following query:1 WITH ProjectList AS 2 ( 3 SELECT 4 Id, 5 Name, 6 Created, 7 (SELECT COUNT(*) FROM UserProjects WHERE ProjectId = p.Id) AS NumberOfUsers, 8 ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber' 9 FROM Projects p 10 ) 11 SELECT * 12 FROM ProjectList 13 WHERE RowNumber BETWEEN 50 AND 60;
This works fine, and give me the results i want. The problem occurs when I want to sort by "NumberOfUsers" which is the results of a sub query.When i say "ORDER BY NumberOfUsers" instead of Id on line 8, I get the following error: Msg 207, Level 16, State 1, Line 10Invalid column name 'NumberOfUsers'. I read this in the documentation: When used in the context of a ranking window function, <ORDER BY Clause> can only refer to columns made available by the FROM clause. An integer cannot be specified to represent the position of the name or alias of a column in the select list. <ORDER BY Clause> cannot be used with aggregate window functions. So this means that what I'm trying to do is not possible. How can I then sort by NumberOfUsers? Is there any other way to achieve this
I need to be able to pass a parameter to a stored procedure indicating which column to sort the outcome by. I cannot simply sort it by the passed variable (or I have the syntax wrong...). The sort can be anyone of eight columns and I need to do this in a fair few places on complex SELECT statements, so I am reluctant to use a case statement, which would make the sp rather large.
I have a SELECT statement in an SP that selects 10 fields, however, i want to be able to pass a variable to the SP to determine which field to ORDER BY.
Is there a way to do this ?
I've tried passing in one of the field names to a variable and then doing ORDER BY @OrderByThisColumn ...nope. I've tried SETting a variable to the above @OrderByThisColumn ...nope.
SELECT H.Fund_Man as Holders, H.Shares as SharesHeld, H.Share_Pric * H.Shares as Value, H.Pcent as SharesOutstanding, H.Shares - H.Shares as ShareChange, C.Reg_Date as ReportDate, 'Register' as Source, ((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital as SectorWeightingPcent, H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital) as OverUnderWeight, (H.Pcent - (((C.Capital / S.CapTotal) * (H.TotalTot * S.CapTotal)) / C.Capital)) * C.isc as SurplusDeficit
FROM Citywatch_Company C Inner Join Citywatch_Holders H On C.Epic = H.Epic Inner Join Citywatch_Sector S On H.Sector = S.Sec_Code WHERE C.Epic = @CompanyCode
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
Hi, I am writing a small search engine. There are two tables. The first one holds the search engine main index, the second one is link table. I have the following query that retrieves results. I would like to sort the results by: dbo.OCCURS2(LOWER(:query),se_links.anchor). se_links.anchor obviously comes from se_links table, so I get an error. Is it possible to done in one query? I'm using MSSQL 2005. Thanks. PS. Function OCCURS2 returns number of occurrences of one string in other.
Code:
select id as Id, uri as ElementUri, size as Size, modified_date as ModifiedDate, title as Title, text as Text, dbo.OCCURS2(LOWER(:query),Title) as TitleOcc, dbo.OCCURS2(LOWER(:query),Text) as BodyOcc FROM se_index WHERE (title LIKE :query) OR (text LIKE :query) OR (id IN (SELECT se_links.target_index_id FROM se_links INNER JOIN se_index AS se_index_1 ON se_links.target_index_id = se_index_1.id AND se_links.anchor LIKE :query))
Hello,Using SQL 2005. Columns:ID, int (PK, auto-increment by 1)WorkHours, intName, varchar(100)I can't seem to get the following query to work. I want to return allNames and the sum of ALL work hours, in each row and order by eachINDIVIDUAL work hour:SELECT Name, SUM(WorkHours) as hFROM EmployersORDER BY WorkHours DESCIt seems that putting WorkHours in but the aggregate function and theORDER BY clause creates a problem.Thank you for your help!
I have a table that I want to re-order the ID column. The ID are not in order now due to some insertion and deletion. What are the steps to re-order the ID column?
