Increment Order
Mar 18, 2008
Hi. I am new in sql and i need help for increment rows. This is my script
I worked in ms sql 2000
SELECT cool.Podchl,cool.Datumchl, cool.Textchl, cool.Timechl,
cast ((substring(cool.Datumchl,7,4)) as int ) as year,
cast ((substring(cool.Datumchl,4,2)) as int ) as month,
cast ((substring(cool.Datumchl,1,2)) as int ) as day,
cast ((substring(cool.Datumchl,13,2)) as int ) as hour,
cast ((substring(cool.Datumchl,16,2)) as int ) as min
FROM zih2.dbo.cool cool
WHERE (cool.Timechl<>'00')
ORDER BY year,month,day,hour,min
I need add new colum, where first colum will be order. I made it but after execute order by I have got 2,3,4,1,5... and i need 1,2,3,4,5,...
View 1 Replies
ADVERTISEMENT
Oct 19, 2005
Hello all,I'm using SS2K on W2k.I'v got a table say, humm, "Orders" with two fields in the PK:OrderDate and CustomerID. I would like to add an "ID" column whichwould be auto-increment (and would be the new PK). But, I would reallylike to have orders with the oldest OrderDate having the smallest IDnumber and, for a same OrderDate, I'd to have the smallest CustomerIDfirst. So my question is:How could I add an auto-increment column to a table and make it createits values in a particular order (sort by OrderDate then CustomerIDhere)?In the real situation, the table I want to modify has around 500krecords and the PK has 5 fields and I want to sort on three of them.Thanks for you helpYannick
View 7 Replies
View Related
Dec 11, 2007
Hello,
I Have a table that needs to have 2 unique number.
detail_id and detail_print_id.
detail_id is already an IDENTITY.
both fields need to be different, because when importing, it imports the same data into a table twice, with only a slight data change (and id is not one of the changes).
So I thought i could do the following:
detail_id INT NOT NULL IDENTITY(1,2),
detail_print_id INT NOT NULL IDENTITY(2,2),
--blah blah
that way, the detail_id will always be odd, and the detail_print_id will always be even. however SQL Server 2005 only allows 1 identity per table, and both these fields need to be auto generated when the field is inserted, so as to prevent double data.
is there anyway I can create a int column to auto increment, without the column being an IDENTITY??
also, I would prefer to not have to create a second table with a single column just for this work.
Thanks,
Justin
View 5 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
Oct 11, 2005
i have got 22,000 rows in a table, i want to update the records to have to start id of 70000 which increments to 70001, 70002 ? how would i go about doing this ?
View 3 Replies
View Related
Sep 30, 2006
Is there an easy and fast way to increment a value by 1 in a database? For example if a value is 106 I will need to make it 107. Is there a fast way to do this?
View 9 Replies
View Related
May 9, 2008
If there is one field such as Seq No in a table and if we entered some data ..
and if we update one record of Seq No then the below records of Seq No should be incremented in that table
for example there are 10 seq no's and if i had updated the seq no 4 to 5 then the
5 shuld b inc 6 and 6 to 7 etc............
at preasent i wrote only
update in sp of that particular record:
like this
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[UpdateSelected]
(
@Description NVARCHAR(30),
@SequenceNo int
)
AS
UPDATE Sequence SET
Description = @Description,
SequenceNo = @SequenceNo
WHERE
SectionID = @SectionID
but now i need additional functionaly like inc the remaining sq no above that given no...
Regards,
Anushka
View 1 Replies
View Related
Jun 12, 2008
1. I have a table lets say ABC in which the datatype of one of the column lets say MNO is int identity.
3. Inserted some records into this table using SSIS. the values in the column MNO are from 1 till 20
2. i want insert more records into this table ABC using SQL task in SSIS
How do i do this. can any body help me out.
Thanks in advance
View 1 Replies
View Related
Jun 12, 2008
1. I have a table lets say ABC in which the datatype of one of the column lets say MNO is int identity.
3. Inserted some records into this table . the values in the column MNO are from 1 till 20
2. i want insert more records into this table ABC
How do i do this. can any body help me out.
Thanks in advance
View 2 Replies
View Related
Sep 7, 2007
I have a table with an automatic count int. What I want to know is there an sql statement to return the next increment from the sequence. Any help would be appreciated.
View 4 Replies
View Related
Mar 8, 2006
TO allI have tabelautonumber value1125364859610712813981010and if i want sum with increment 2autonumber value1 - 2 1 + 5 = 63 - 4 6 + 8 = 145 - 6 197 - 8 259 - 10 18please help.............. trims
View 5 Replies
View Related
Oct 1, 2007
Hi,
This is my first SSIS query. I have just started with ssis and have the following task
I am looping text files in a folder using foreach. I transfer text file data to sql server. I want to count the total rows are that transferred in all the text files after the foreach loop finishes. I user RowCount but it gives count of only current textfile. It gets reset each time the loop iterates. I want something like @count = @count + currentnoofrows in current text file.
lalit.
View 5 Replies
View Related
Nov 21, 2006
I'm using SQL Server 2000 with MS Management Studio. I have a table that has 400 rows. I have setup the Primary key to increment automatically. How do I setup so that the next record starts at 4001 and up? Right now it starts at 1274, for example. So besides dropping the table and re-creating it, how do reset it so it counts from sequentially from the last row inserted?
View 5 Replies
View Related
Jul 19, 2007
How can you increment row by row through a table using a stored procedure?
I am trying this but getting error on SET statement - Line 6: Incorrect syntax near '.'.
PROCEDURE asuodem.spA_LinkSpinner ASWHILE (tblLinkInfo_OLD2.L_ID IS NOT NULL)BEGINIF (tblLinkInfo_OLD2.L_Rank > 10) AND (tblLinkInfo_OLD2.L_Rank < 300)SET tblLinkInfo_OLD2.L_Rank = Convert(int, (250-25+1)*RAND())ELSE CONTINUEENDRETURN
View 10 Replies
View Related
Mar 24, 2008
DIAGID is the
field in database which is integer. i want to increment this when page is
loaded.but it is not working..
plz
find mistake ... thanks in advance
SqlCommand sqlCmd = new SqlCommand("Select max(DIAGID) from tblDxactual",
sqlCon);
SqlDataReader sqlDr;
sqlCon.Open();
sqlDr = sqlCmd.ExecuteReader();
if (sqlDr.Read())
{
txtbxDiagID.Text = sqlDr[0].ToString()+ "1"
;
}
else
txtbxDiagID.Text="1";
sqlCon.Close();
View 3 Replies
View Related
Feb 4, 2005
It's been a long time since I've had to check an index for the highest value, then add 1, to create a new unique key. These past few years, it seems this is usually done for you. But now that I'm working with MS-SQL, I don't see it. Is it there? It's doesn't seem to be inherent in the definition.
View 5 Replies
View Related
Jan 26, 2006
Hello,
Firstly Hello to everyone I'm new the forum and fairly new to .net
I'm working on web datbase application using visual studios 05 and MS SQL05 I've used 2003 (briefly) before but 2005 is very new to me.
To my problem I download the GUI interface from microsoft so I can now setup a local database and do my own testing.
I have created the table and fields with in it however on a particular table i have made a primary Key and left it as an INT but I would like to set it as auto increment ! I dont know how to select that option as i was used to mysql way of doing things or does this have to be done as a stored procedure ?
Any assistance much appreciated.
View 1 Replies
View Related
