Simple Query To Create Column Of Sequential Numbers
May 2, 2006
Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?
e.g given
intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35
change it to:
intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35
I think it might need a stored proc..
Many Thanks
greg
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq
View 1 Replies
View Related
Oct 25, 2013
I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.
Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?
View 3 Replies
View Related
Feb 18, 2008
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
View 6 Replies
View Related
Apr 27, 1999
I have a query which returns information about transactions similar to this:
Select account, trans_code, quantity
from ledger_table
This returns something like:
acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.
What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:
acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.
The lot number is reset for each grouping.
Does anyone have a way to do this outside of a cursor or temp table?
TIA, any help greatly appreciated.
View 1 Replies
View Related
Jul 20, 2005
I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?
View 6 Replies
View Related
Apr 5, 2014
I have a problem. In my database I have the following numbers available:
101
104
105
110
111
112
113
114
What I need is to get a select query with records and sequentials numbers after it like:
101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0
How can I do It?
View 2 Replies
View Related
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Aug 17, 2015
create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked) from table
View 5 Replies
View Related
Aug 17, 2015
I need to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table
View 3 Replies
View Related
Mar 17, 2004
I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.
View 4 Replies
View Related
Jul 23, 2005
I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com
View 2 Replies
View Related
Jan 15, 2015
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
View 5 Replies
View Related
Feb 18, 2014
I'm trying to create a column of numbers that increment by one.
I'm not able to use a #temptable in the application I'm using so I cannot use IDENTITY(int,1,1).
I want to add an Id column to this query:
Select distinct sd.name,ic.TABLE_SCHEMA,ic.TABLE_NAME from sys.databases sd
cross join INFORMATION_SCHEMA.COLUMNS ic
where sd.name = 'ODS1stage'
order by TABLE_SCHEMA,TABLE_NAME
How can I accomplish this without creating a temp table? I would just alter the table and insert the numbers but there are 2000 rows.
View 7 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Nov 30, 2005
Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
View 4 Replies
View Related
Feb 28, 2007
Hi all,I might be getting this all wrong but bear with me.
I need to create some kind of Unique field in my DB that is
nonsequential. This is because I need it to be difficult to guess
ids if you have an example in front of you.I have looked at
8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me
how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN)
)
The "T" serves to split the numbers for when I am converting back.
So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) )
------------------------------------------------------------------------------------------
3166 00031663 C5ET7BAF
3167 00031673 C5FT7BB9
3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?)
What do people think?
Thanks,Pete
View 4 Replies
View Related
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
May 8, 2012
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
View 3 Replies
View Related
Jul 23, 2005
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
View 9 Replies
View Related
Feb 17, 2005
Hello everyone,
I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.
I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.
Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.
Thanks for your help. If you know this is impossible, that would be helpful feedback as well.
BlackCatBone
View 3 Replies
View Related
Aug 9, 2014
I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.
The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314
Desired out put:
From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314
I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:
SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'
[Code] ...
View 6 Replies
View Related
Sep 2, 2015
I have a data with mutliple esn but different auditdate and opid. I will pull this data filtering by date and opid. My requirements is not to include the opid = 51 but need to get the desired opdesc for this esn that contains opid=51.
See below sample ddl and desired result. I dont want do include the opid = 51 because it will create a duplicate in transaction instead retain the opid 5
example: esn T9000000000019829505 has multiple rows with different auditdate and opid. retain the records for opid is equal to 5 but get the opdesc for opid is equal 51.
reate table #test
(esn nvarchar(35), dateaudit datetime, opid int)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-05-12 20:32:39.490',5)
insert into #test(esn,dateaudit, opid)values('352452060499834','2015-07-06 17:35:14.210',5)
insert into #test(esn,dateaudit, opid)values('T9000000000019829505','2015-01-14 15:18:45.620',5)
[Code] ....
Desired Result:
esn-------------------dateaudit----------------opid--opdesc--rn
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1
T9000000000019829505--2015-01-14 15:18:45.620--5---Scrap-----1
OR
esn-------------------dateaudit----------------opid--opdesc--rn--remarks
352452060499834------2015-05-12 20:32:39.490---5---Shipping--1---shipping
352452060499834------2015-07-06 17:35:14.210--5---Shipping--1---shipping
T9000000000019829505--2015-01-14 15:18:45.620--5---Shipping--1---Scrap
View 5 Replies
View Related
Apr 7, 2004
Hello!
Got a problem I hope some clever people can help me out with..
I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this
PageNum ItemNum ItemDescription
----------- ----------- -------------------
1 1 aaaaaaa
1 2 bbbbbbb
1 3 cccccccc
2 1 ddddddd
2 2 eeeeeee
2 3 ffffffffffff
3 1 ggggggg
3 2 hhhhhhh
3 3 iiiiiiiiiiiiiiii
The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).
This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:
DECLARE @intCounter int
SET @intCounter = 0
UPDATE <Item Table>
@intCounter = ItemNum = @intCounter + 1
WHERE Pagenumber = <Currently Selected Page>
However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:
DECLARE @intCounter int
SET @intCounter = 1
UPDATE <Itemtable>
SET @intCounter = PageNum = CASE
WHEN @intCounter = PageNum - 1 THEN @intCounter + 1
WHEN @intCounter = PageNum - 2 THEN @intCounter + 1
ELSE @intCounter
END
But this doesn't quite work.
Anyone got any other ideas??
Thanks
Greg
View 3 Replies
View Related
Dec 27, 2013
s it possible to create random numbers within a range like : .28 to .57 ?
UPDATE Nop_ProductVariant
SET percent = RAND() .28 to .57
So the percent field would look like :
ProdID percent
1001 .38
1002 .29
1003 .40
etc . .
all rows having a different value
View 12 Replies
View Related
Jun 9, 2007
SELECT pid, lname, visit_date, quantity
FROM customer
ORDER BY pid
pid lname visit_date quantity
-------------------------------------------
23 wang 07/23/2006 100
23 wang 07/30/2006 140
23 wang 08/05/2006 130
23 wang 08/15/2006 135
23 wang 08/22/2006 110
34 linden 06/23/2006 99
34 linden 07/06/2006 110
34 linden 07/15/2006 120
34 linden 08/26/2006 99
How do I use cursor (or is there a better way) to create an additional column "index"? "Index" starts at 1 for every unique pid
index pid lname visit_date quantity
----------------------------------------------------------
1 23 wang 07/23/2006 100
2 23 wang 07/30/2006 140
3 23 wang 08/05/2006 130
4 23 wang 08/15/2006 135
5 23 wang 08/22/2006 110
1 34 linden 06/23/2006 99
2 34 linden 07/06/2006 110
3 34 linden 07/15/2006 120
4 34 linden 08/26/2006 99
Please let me know if I can explain the my question better. Thanks in advance
View 4 Replies
View Related
Mar 12, 2015
I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]
/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market
How can I do this?
View 1 Replies
View Related
Mar 21, 2002
Hi
I'm trying to find a decent way of multiplying a set of numbers in a column without using a cursor in T-SQL.
There is no 'Product' aggregate function that I'm aware of in SQL 7 or 2000. The workaround I'm currently using is this :
SELECT EXP(SUM(LOG(ColumnName))) FROM tblName
This works fine, except when negative numbers are introduced. The LOG function does not allow negative numbers and therefore returns a domain error and the negative number is eliminated from the aggregate.
I could use a cursor to do the multiplication, however, this is proving too slow for the bulk calculations involved.
If anyone has any ideas or suggestions, then that would be much appreciated.
thanks....Tom
View 1 Replies
View Related
Jul 19, 2007
Hello.
Whats the easiest way to add a column with a numeration to my table?
I want the users to have an easier life finding on which row they are
talking.
I preffer of course doing it in reporting service and not adding a
column in my dataset.
Thanks.
View 1 Replies
View Related
Apr 23, 2008
OK I HAVE AN INTERESTING SITUATION HERE.
I HAVE AN SSN COLUMN.
SSN NUMBER HAS 9 NUMERIC DIGITS.
NOW IN THE SSN COLUMN I HAVE A LOT OF BAD SSN WHICH CONTAIN ONLY 3 OR ONLY 4 OR ONLY 5 AND SO ON DIGITS.
HOW CAN I FILTER THESE ONES OUT SO THAT I CAN MAKE AN EXCELL REPORT OF THIS DATA.
I AM THINKGING OF USING CONDITIONAL SPLIT.
BUT HOW SHOULD I GO ABOUT IT.
PLEASE LET ME KNOW THE COMMAND
View 1 Replies
View Related
Oct 11, 2007
Hi,
I have the following tables :
Code Block
Create table #EmployeeList(empname nvarchar(20), emptype char(5))
Insert INTO #EmployeeList('Cary zzz',null);
Insert INTO #EmployeeList('01 Jack',null);
Insert INTO #EmployeeList('02 Tommy',null);
Insert INTO #EmployeeList('03 Ricardo',null);
Insert INTO #EmployeeList('04 Jack',null);
Insert INTO #EmployeeList('Les zzz',null);
Insert INTO #EmployeeList('05 Tim',null);
The final data looks like this :
Cary zzz NULL
01 Jack NULL
02 Tommy NULL
03 Ricardo NULL
04 Jack NULL
Les zzz NULL
05 Tim NULL
1. I want to delete all rows which have 'zzz' in it.
2. I want to remove the numbers from the empname column
Code Block
Expected Output :
Jack NULL
Tommy NULL
Ricardo NULL
Jack NULL
Tim NULL
Can anyone help me please with the query?
thanks.
View 4 Replies
View Related
Aug 27, 2004
HI all;
I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.
Any help would be highly appreciated, thanks all.
View 1 Replies
View Related
Oct 8, 2013
I have a Column which has both numeric values and Alphabets in them.I want to sort them in descending with numbers first and then Alphabets later.
Example:
Source column:
2008
2005
2010
2013
All-Year
Month-year
Required Output:
Source Column:
2013
2010
2008
2005
Month-Year
All-year
View 4 Replies
View Related
