Need Another Pair Of Eyes Sql 'Order By'
Feb 9, 2006
my page suddenly stopped working when I wasn't working on it and it seems to be down to the 'ORDER BY' part of my SQL. I'm here alone as usual and I need someone to glance at the sql strings below. (yes, I do need the select *)
If I run this in SQL Manager it works fine:
SELECT * from dest_search WHERE trip_type like 'Trekking' ORDER BY start_date
if I do the same from my asp page it fails but if I leave out 'ORDER BY start_date' it works.
the error I get is:
Microsoft OLE DB Provider for SQL Server error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/Newindex/trip_types.asp, line 53
line 53 is the 'desc = oRS...' bizarrely
oRS.Open strSQL, oConn, 2, 3
oRS.moveFirst
Do while not oRS.eof
country = oRS("country")
53---> desc = oRS("description")
url_link = oRS("url_link")
startDate = oRS("start_date")
endDate = oRS("end_date")
trip = oRS("trip_type")
difficulty = oRS("difficulty")
not all the descriptions are filled in (some are null) but that doesn't stop SQL manager from working or unordered results coming up fine in my web page.
any comments gratefully received thanks.
View 6 Replies
ADVERTISEMENT
Apr 10, 2007
This is more of a "does anyone see something I'm missing" post versus a real problem.
What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.
I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
DECLARE @SQLSTRING VARCHAR(2000)
DECLARE @DBNAME VARCHAR(64)
DECLARE @tablename varchar(128)
DECLARE @execstr varchar(255)
DECLARE @objectid int
DECLARE @indexid int
DECLARE @frag decimal
DECLARE @maxfrag decimal
DECLARE @maxextfrag decimal
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 30.0
SELECT @maxextfrag = 40.0
DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')
---- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)
OPEN db
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @DBNAME
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @execstr = 'USE ' + @dbname + ';' + char(13)
PRINT @execstr
EXEC (@execstr)
-- Open the cursor.
OPEN tables
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables
SELECT @SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @SQLSTRING = @SQLSTRING + @DBNAME
SELECT @SQLSTRING = @SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag'
PRINT @SQLSTRING
EXEC(@SQLSTRING)
FETCH NEXT
FROM db
INTO @DBNAME
END
CLOSE db
DEALLOCATE db
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
OR ExtentFrag >= @maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
---- Delete the temporary table.
DROP TABLE #fraglist
Again, thanks!!
View 4 Replies
View Related
Sep 13, 2007
when I try to create this SP I get: "incorrect syntax near @MyResult"
I have tried INT and different variable names, but get same error.
CREATE PROCEDURE sp_IsValidLogon
@UserName varchar(16),
@Password varchar(16) ,
@MyResult varchar(3) OUTPUT
As
if exists(Select * From User_Table
Where UserName = @UserName
And
Password = @Password)
begin
@MyResult = 1
end
else
begin
INSERT INTO FailedLogons(UserName, Password)
values(@UserName, @Password)
declare @totalFails int
Select @totalFails = Count(*) From FailedLogons
Where UserName = @UserName
And dtFailed > GetDate()-1
if (@totalFails > 5)
UPDATE User_Table Set Active = 0
Where UserName = @UserName
@MyResult = 0
end
View 3 Replies
View Related
Apr 20, 2006
greets, im coding a few queries to a table. im storing sets of records into the table, each set of records will haev a different batch id. so basically 2 sets of records can occupy this table at the same time, and their batch id is the main key (with 2 other fields also being PKs). i want to compare the 2 sets in the same table and get the differences:
1. records that were added
2. records that were updated
3. records that were deleted
ive written queries for the added records, and the updated records but i cant get the query for finding deleted records. the logic looks good to me but im obviously missing something so i could use a fresh pair of eyes.
here is the table def:
Code:
CREATE TABLE [dbo].[UPCXREF_BATCH] (
[BATCH_ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CHAIN_CODE] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UPC] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ITM_CODE] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ITM_CATEGORY] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREATE_DATE] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UPCXREF_BATCH] WITH NOCHECK ADD
CONSTRAINT [PK_UPCXREF_BATCH] PRIMARY KEY CLUSTERED
(
[BATCH_ID],
[CHAIN_CODE],
[UPC]
) ON [PRIMARY]
GO
and here are the queries ive gotten so far
Code:
/* This section retrieves all UPDATED records */
SELECT 'U' as FLAG, b2.CHAIN_CODE as CHAIN_CODE, b2.UPC, b2.ITM_CODE, b2.ITM_CATEGORY
FROM UPCXREF_BATCH b2 INNER JOIN UPCXREF_BATCH b1
ON b2.CHAIN_CODE=b1.CHAIN_CODE
AND b2.UPC=b1.UPC
AND (b2.ITM_CODE<>b1.ITM_CODE OR b2.ITM_CATEGORY <> b1.ITM_CATEGORY )
WHERE b2.BATCH_ID='BTC0002' AND b1.BATCH_ID='BTC0001'
/* This section retrieves all NEW records */
SELECT 'A' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0002'
AND (CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001')
OR UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0001'))
here was my attempt to get deleted records which looks like it makes sense but isnt working
Code:
SELECT 'D' as FLAG, CHAIN_CODE, UPC, ITM_CODE, ITM_CATEGORY
FROM UPCXREF_BATCH
WHERE BATCH_ID='BTC0001'
AND CHAIN_CODE NOT IN (SELECT CHAIN_CODE FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
AND UPC NOT IN (SELECT UPC FROM UPCXREF_BATCH WHERE BATCH_ID='BTC0002')
here batch 'BTC0001' is the older set of records that already existed and batch 'BTC0002' is the new set we just inserted into the table. am i missing something else?
View 11 Replies
View Related
Jun 4, 2007
I have three tables:
Category: category id, category name, more…
Topic: topic id, topic name, category id, more…
Post: post id, post text, topic id, more…
I need help with a query to display the following:
Category name, # of topics, # of posts
Example:
Category.........................Topics.....Posts
SQL Stored Procedures.........12........562
It’s coming along but there are some problems, ASP.NET actually has 2 posts not 1. And the java totals are correct but it should be Java, 3, 10 (all in one line)
Category.....Topics...Posts
ASP.NET.........2........1
C#................1........1
Java..............1........1
Java..............1........2
Java..............1........7
Overview: use category id to get count of topics then use the topic id to get the count of posts.
SELECT C.CategoryName, T.ThreadCount AS Threads, T.PostCount AS Posts
FROM Category AS C LEFT OUTER JOIN
(SELECT tt.CategoryID, PostID.PostCount, COUNT(tt.ThreadName) AS ThreadCount
FROM Thread AS tt LEFT OUTER JOIN
(SELECT ThreadID, COUNT(PostID) AS PostCount
FROM Post AS P
GROUP BY ThreadID) AS PostID
ON tt.ThreadID = PostID.ThreadID
GROUP BY tt.CategoryID, PostID.PostCount) AS T
ON C.CategoryID = T.CategoryID
WHERE (C.CategoryID = T.CategoryID)
GROUP BY C.CategoryName, T.ThreadCount, T.PostCount
ORDER BY C.CategoryName
Thanks in advance
View 6 Replies
View Related
Mar 12, 2008
Hi,I need to display a dataset where everything is dynamic.e.g. I have a table with columns "Code", "Description" and "Inspected" andanother table with columns "UserCode", "Name", "PostCode" and "Town" etcAnd I need to dislay data like this from a single db proc with parameters:-(TableName, ColumnName, ColumnValue)Procedure called with these parameters (CodeTable, Code, TD001) would returna dataset like this:-----------------------------------|Code | TD001|Description| Printer|Inspected | Y---------------------------------Not----------------------------------|TD001|Printer|Y---------------------------------Procedure called with these parameters (UserTable, UserCode, CP1) wouldreturn a dataset like:----------------------------------|UserCode | CP1|Name | Charles|PostCode | 2000|Town | Sydney---------------------------------Not---------------------------------|CP1| Charles| 2000| Sydney---------------------------------Any ideas how I would code the database proc, I did consider using XML butnot sure.ThanksAJP
View 3 Replies
View Related
Sep 17, 2004
Hi folks,
I am trying to write a query to get data in pairs, for example, i have data like this:
sr_no week_no
1 24-A
2 24-B
3 24-C
4 25-A
5 25-B
6 26-A
7 26-B
I want to get data in pairs i.e. data for week_no 24-A and 24-B will come togather? is it possible?
Any urgent help will be highly appreicated.
Thanks
View 9 Replies
View Related
Jul 20, 2005
I want to store many different types of objects in a single table. Iwas thinking of using the name value pair approach to achieve this.Does anybody have any experience with a such a design?The table might look like thisCREATE TABLE NV (pk int, type int, [name] varchar(100), valuevarchar(100))--Insert a manager - type = 1INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'FirstName', 'John')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'LastName', 'Smith')INSERT INTO NV (pk, type, [name], val)VALUES (11, 1, 'Position', 'CEO')--Insert an employee - type = 2INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'FirstName', 'Joe')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'LastName', 'Blog')INSERT INTO NV (pk, type, [name], val)VALUES (21, 2, 'Position', 'Developer')--Insert an inventory item - type = 3INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Name', 'Chair')INSERT INTO NV (type, [name], val)VALUES (13, 3, 'Color', 'White')INSERT INTO NV (type, [name], val)VALUES (3, 3, 'Price', '$150')
View 3 Replies
View Related
Aug 8, 2007
Hi,
I have to log the Details of the incoming xml message into databse.
But the values logged will vary with the message.So I cant fix the mumber of columns.
I thought of using table in which the fields are logged as Key-Value Pairs. The table looks as below.
TransactionID ColumnKey ColumnValue
1111 PONumber 123
1111 Sender xxx
1111 Recever yyy
using dynomic query i was able to get the results as follows
TransactionID PONumber Sender Receiver
1111 123 xxx yyy
Till now every thing was fine. but now i got new requirement where i have to identify each column with its parent. For example if we consider the line items of the PO, table may look like below.
TransactionID ChildKey ChildValue ParantKey ParantValue
1111 PONumber 123 null null
1111 Sender xxx null null
1111 Recever yyy null null
1111 ItemName soap ItemID 123
1111 Quantity 4 ItemID 123
1111 UnitPrice 2.2 ItemID 123
1111 ItemName Brush ItemID 222
1111 Quantity 5 ItemID 222
1111 unitPrice 4.4 ItemID 222
I am unable to design the database which satisfy the requirement of the reporting.
I not even know how to query the data which is logged like this.
Help me by giving the inputs to design databse for the above problem and to query the data .
advance thanks
Srinivasa Mahendrakar
View 4 Replies
View Related
Sep 8, 2007
I have a table, gdbdoc, that contains record-key pairs, linking records in another table. There is no significance in the order of the link: if records A and B are linked, then I don't care whether the link is A -> B or B -> A, and my normal query logic is SELECT ... Where DCIindiid = A ... union SELECT ... Where DCILinkid = A(DCIindiid = key1, DCILinkid = Key2)
The link-creation process normally checks whether there is already a link in either direction. Thus before creating a link A->B the logic checks to see whether either the A->B or B->A link record exists, and a new link is not created if the link already exists in either direction. However recently one of my processes bypassed the reverse-link check, and I've ended up with a few hundred cases where there is both an A->B link and a B->A link.
If I run a query: - select gd1.* from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
this displays all the records where one record links A -> B and there is also another record that links B -> A.
How do I write a query to delete ONE of the pair of duplicate records? I have two problems: -
Problem 1: Table gdbdoc is keyed on (DCIindiid, DCILinkid). Both guids are needed to create a unique key, and the table does not have a single key field. You can't write DELETE gdbdoc where DCIIndiid, DCILinkid IN select gd1.dciindiid, gd1.linkid from gdbdoc as gd1 join gdbdoc as gd2 on gd1.dciindiid = gd2.dcilinkid and gd1.dcilinkid = gd2.dciindiid
as the DELETE ... SELECT ... syntax only seems to support a single returned value.
Problem 2. If we solved problem 1, we would (I think) delete BOTH the A->B link and the B->A link , whereas I only want to delete one of these links.
Afterthought: Problem 2 seems easily solvable: add "Where gd1.DCIindiid < gd1.DCILinkid" to the DELETE ... statement. Although the concept of "<" doesn't really mean anything with a guid, this is accepted by SQL, and halves the number of records returned by the select. Obviously I don't care which of the two links (A->B or B->A) is deleted.
Regards, Robert Barnes
View 3 Replies
View Related
Apr 29, 2008
Hi Folks,
I would like to create a table with primary key pair:
Key1 : nchar(10)
Key2: nchar(10)
Value: money
That is, Key1 and Key2 are the primary key columns for the table. I would like to think of (Key1='Foo', Key2='Bar') to be the "same" as (Key1='Bar', Key2='Foo'). Is there a way to enforce this as a table constraint, or do I have to enforce this manually in all procedures that modify and read the table?
Thanks!
Adam Cataldo
View 1 Replies
View Related
Jan 18, 2005
I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.
Table of course takes this form key | value
---------------------------------
config_setting1 | value1
config_setting2 | value2
I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object???
Thanks for any help and suggestions,
ecolner@yahoo.com
View 1 Replies
View Related
Oct 12, 2015
I can only do one match at a time -- Like can only do either the sql_statement_(start and end), or sp_statement_(start or end). Is there any way to capture both in the same session? Or since I am adding both the events in the ADD EVENT section, can I query it somehow to get unmatched SP or SQL?
Code:
USE master;
GO
-- Create the Event Session
IF EXISTS(SELECT *
FROM sys.server_event_sessions
WHERE name='TimedOutSQL')
[code]...
View 2 Replies
View Related
Sep 22, 2015
my business user want all record where glcode must start with 2 and 4
<sample data
Tran_No
GLCode
abcd123
2123
abcd123
21235
abcd123
4289
[code]....
View 6 Replies
View Related
Sep 28, 2007
I am trying to create an exception report that will show the difference between two versions of the same row. (Combination of two different sources in sql, with source 1 having childID = 0 and the other source having childID = 1; parentID is the link between them)
The results are as follows:
ParentID - ChildID - Col1 - Col2 - Col3
1 - 0 - AA - BB - CC
1 - 1 - AA - BF - CC
2 - 0 - GG - NN - TT
2 - 1 - DE - NN - TA
3 - 0 - etc
3 - 1 - etc
4 - etc
How can I render the differences in red in RS?
View 1 Replies
View Related
Apr 18, 2008
How can we do pair wise comaprission using Sub query as generally can be done in Oracle?
Thanx
View 4 Replies
View Related
Jan 7, 2007
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
View 7 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 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
Jan 4, 2008
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!
View 14 Replies
View Related
May 8, 2007
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez
View 4 Replies
View Related
Apr 14, 2008
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?
View 14 Replies
View Related
Jul 20, 2005
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View 2 Replies
View Related
Apr 14, 2008
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?
View 5 Replies
View Related
May 18, 2006
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,
View 16 Replies
View Related
Mar 19, 2007
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.
Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS
for example,
create table test_sort
( description varchar(75) );
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));
Resultset1
----------
Non-A
Non-O
Noni
Nons
Resultset2
----------
Non-A
Noni
Non-O
Nons
Any ideas?
View 4 Replies
View Related
Mar 27, 2008
I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?
View 4 Replies
View Related
Jul 5, 2007
I noticed the StockDate is not sorted in proper order, like ascending order...
Code:
select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc
I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...
The sample data is MM/DD/YYYY...
So, how do I get it ordered propery by Year, Month then Day??
View 2 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
Apr 16, 2008
Hi,
I have some hierarchical data in a table. Say for example:
Parent Child
------------------------
NULL 1
1 2
1 3
2 4
2 5
3 6
3 7
5 8
5 9
7 10
7 11
11 12
11 13
Now I want to be able to use CTE's to be able to traverse this tree in
1) level by level order 1,2,3,4,5,6,7,8,9,10....
2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...
What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.
with Tree (id)
as
(
select id from WithTest
where parent is null
union all
select a.id
from Tree b join WithTest a
on b.id = a.parent
)
select * from Tree
Any ideas? Thanks.
View 3 Replies
View Related
Jan 8, 2008
Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.
Any ideas?
Thanks,
Assaf
View 1 Replies
View Related
