How To Change The Order Of Rows In Datatable Randomly?
Mar 26, 2006
Hello!
I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100))
Thanx for any idea,
Z.
View 4 Replies
ADVERTISEMENT
Jun 21, 2012
I need to randomly order the selected rows from my table. Is this better to do on the Application level or in a stored procedure using "orderby NewID()"?
Which is faster? There will be about 100 rows returned with 10 columns.
View 5 Replies
View Related
Jun 13, 2002
Say you have a table that contains 1000 rows, you create a simple select statement with a where clause that returns 100 or so of those rows. Easy enough.
Now, let's say that you wish to modify that select so that out of the 100 rows that match the where clause criteria, you only wish to return 10 rows randomly (i.e., you could run this query multiple times and get different results). How exactly would you go about doing this, efficiently?
I've thought about creating a stored procedure that will query the initial 100 rows into a temp table with an additional column (to number the rows from 1 to 100). Then setting up a loop (10 iterations) that will then generate a random number from 1 to 100 and select that row number into another temp table. At the end of the loop I'll have my table of randomly selected records. I am sure there is probably a better way to do this...
~Mike
View 2 Replies
View Related
Nov 9, 2004
Hi All
by using this query
"select * from sample order by newid()" im getting a set of rows. On refreshing this query i need the same set of rows to validate.(provided sufficient data in the table).
Please provide me the query to use for this
Adv. Thanks
Hari...
View 2 Replies
View Related
Dec 21, 2006
Hi ,
i am dealing with around 14000 rows which need to be put into the sql destination.,But what i see is that the order of the rows in the desination is not the same as in the source,
However it is same for smaller number of rows.
Please help ...i want the order to be same.
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
Sep 3, 2007
Filling a DataTable from SqlQuery : If SqlQuery returns some null values problem ocurrs with DataTable.
Is it possible using DataTable with some null values in it?
Thanks
View 2 Replies
View Related
Aug 25, 1999
Hi,
Is there any way to change the sort order from case sensitive to case insensitive in SQL Server 6.5 without having to rebuild the master db and all users db's.
thanks in advance.
View 1 Replies
View Related
Oct 26, 2000
hi,
if I change the sort order after I install sql server, I have to rebuild the databases. the meaning of rebuilding the Databases is to create a complete database from scrach including tables, store procedures data. or what is the meaning of rebuilding the databases
Ahmed
View 2 Replies
View Related
Aug 17, 2000
I have just started with 7.0 and found that one of my enviornments is not the same as production. I am trying to change the Char. set and sort order(rebuild master). In 6.5, install the CD and just update the options. In 7.0, do I have to un-install to do this? I looked on the CD and the local directory and can not seem to find the options to update the master db?
Thanks in Advance,
View 2 Replies
View Related
Feb 5, 2007
Im new to sql server 2000 can i change the order in the database is it possible. If anybody help me how to do it that would be a great help.
Articles on various categories
View 2 Replies
View Related
Apr 14, 2008
I'm using VBE 2008 and in the edit table schema box it doesn't allow reordering the columns. Any solutions?
View 1 Replies
View Related
Jan 23, 2008
Hello everyone.
You may had this problem before and know how to do that or may be can suggest me with some ideas.
I am in a process of creating a report wich has 3 groups and the data at the moment grouped by Company - Product - Customer.
I want to be able to change grouping order and respectively the data in report , something like Product - Company - Customer or Customer - Product- Company. (just example)
Is that possible to do at all?
May I add some drag and drop functionality and integrate in my report ?
Any ideas will be most welcomed
Thanks in advance
View 6 Replies
View Related
Jul 9, 2002
Hi,
We ae planning to upgrade SQL 7 to SQL 2000 curretly we have BInar sort order.We have to cahnge the sort order to Dictionary sort order in SQL 2000 as per the recomendation from application vendor.
What is the best way Ican do this? Can I cahnge the sort order at the time of upgrade without effecting the data?
Thanks in advance
Mohan
View 1 Replies
View Related
Jun 6, 2007
Hello.
Hope you are all well and having a good day.
I've got a problem i would like you to help me out with. My
company has got a client who sells meat and my senior tech lead
has customized the cart to fit their needs and have created a
despatch table with a "simple asp form" that allows them
to scan their products in with a wi-fi scanner.
The despatch table has the following fields:
1. OrderID
2. Product
3. Quantit
4. sellbydate
5. traceabilitycode and
6. Weight
Question 1: how do i create a form in asp that allow a user to
view the order no, product and quantity
celibate,traceabilitybycode and Weight after scanning the
details into the simple form created?
Question 2: How can i create an asp form that allows users to
search for the Order that populates the orderid, Product,
quantity?
Question 3: How can i create an asp for that allows users to
search for Product with orderid,traceabilitycode and quantity?
Question 4: How can i create an asp form that allows users to
Trace: Orderid, traceabilitycode, sellbydate and Quantity?
Please find below the source code of both the:
despatchLotsRowsSimpleForm.asp that allow them to scan details
into the table and the despatchLotsExec.asp:
THE FORM:
<!#include file="../includes/settings.asp">
<!#include file="../includes/getSettingKey.asp">
<!#include file="../includes/sessionFunctions.asp">
<!#include file="../includes/databaseFunctions.asp">
<!#include file="../includes/screenMessages.asp">
<!#include file="../includes/currencyFormat.asp">
<%
on error resume next
dim mySQL, connTemp, rsTemp
' get settings
pStoreFrontDemoMode =
getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")
pAuctions = getSettingKey("pAuctions")
pAllowNewCustomer = getSettingKey("pAllowNewCustomer")
pNewsLetter = getSettingKey("pNewsLetter")
pStoreNews = getSettingKey("pStoreNews")
pSuppliersList = getSettingKey("pSuppliersList")
pRssFeedServer = getSettingKey("pRssFeedServer")
%>
<b><%=getMsg(10021,"despatch")%></b>
<br>
<%=getMsg(10024,"Enter despatch ...")%>
The despatchExec.asp Page
<%
' WebShop 3.0x Shopping Cart
' Developed using ASP
' August-2002
' Email(E-Mail address blocked: See forum rules) for further information
' (URL address blocked: See forum rules)
' Details: add e-mail to newsletter list
%>
<!--#include file="../includes/settings.asp"-->
<!--#include file="../includes/getSettingKey.asp"-->
<!--#include file="../includes/databaseFunctions.asp"-->
<!--#include file="../includes/stringFunctions.asp"-->
<!--#include file="../includes/miscFunctions.asp"-->
<!--#include file="../includes/screenMessages.asp"-->
<!--#include file="../includes/sendMail.asp"-->
<%
on error resume next
dim mySQL, connTemp, rsTemp, pEmail
' get settings
pStoreFrontDemoMode = getSettingKey("pStoreFrontDemoMode")
pCurrencySign = getSettingKey("pCurrencySign")
pDecimalSign = getSettingKey("pDecimalSign")
pCompany = getSettingKey("pCompany")
pEmailSender= getSettingKey("pEmailSender")
pEmailAdmin= getSettingKey("pEmailAdmin")
pSmtpServer= getSettingKey("pSmtpServer")
pEmailComponent= getSettingKey("pEmailComponent")
pDebugEmail= getSettingKey("pDebugEmail")
pGiftRandom= getSettingKey("pGiftRandom")
pPercentageToDiscount= getSettingKey("pPercentageToDiscount")
pStoreLocation = getSettingKey("pStoreLocation")
' form
pidOrder = lcase(getUserInput(request.form("idOrder"),50))
pProduct = lcase(getUserInput(request.form("product"),50))
pQuantity = lcase(getUserInput(request.form("quantity"),50))
pPriceToDiscount = 0
' insert despatch
mySQL="INSERT INTO despatch2 (idOrder, product, quantity ) VALUES ('"
&pidOrder& "', '" &pProduct& "', '" &pQuantity&
"')"
call updateDatabase(mySQL, rstemp, "despatchExec")
pBody=getMsg(10025,"Despatch confirmed...") &VBcrlf&
getMsg(311,"To opt-out click at:") & " (URL address blocked: See
forum rules)="&pEmail
response.redirect "redit_message.asp?message="&Server.Urlencode(getMsg(10025,"Despatch confirmed"))
call closeDb()
%>
Thank you for your help in advance..
Regards,
philip
View 2 Replies
View Related
Sep 17, 2014
I am creating dynamic pivot and my column order is as below
[2015-02],[2015-04] [Prior] ,[2014-08],[2014-11]
but i want to display as below:
[Prior],[2014-08],[2014-11],[2015-02],[2015-04]
View 1 Replies
View Related
Jan 23, 2008
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())
View 5 Replies
View Related
Jul 14, 2015
Question re Merge rep (pull) and processing order. We have a group of changes associated with an app upgrade, the scripts run fine on the publisher. Part of the change includes creation of a new table , followed by altering a view to use new table.Following the change at the publisher, when the sync is kicked off from the subscriber, it fails - the alter of the view throws an'invalid object' error with regard to the new table. Seems as if the view alter is attempted before the dependant table has been created.
I have tried to amend the processing order of the view using sp_changearticle, which executes (quickly) with a 0 return code.But it is to no avail , the error still occurs. is it possible to change the processing order for a view article , which will be applied to schema changes ? Have
View 4 Replies
View Related
Jul 22, 2015
How to Change Order of Column In Database Tables
View 10 Replies
View Related
Feb 26, 2008
Say you have an existing populated SQL 2005 database, with 700+ tables, and you want to just change the order of the columns inside every table. Short of manually building conversion scripts, anyone know an automated way to do this? I was thinking thru ways to do them all in one shot, and have tools like Erwin and DbGhost that could be used also. Basically moving some standard audit columns from the end of the tables to just after the PK columns.
Thanks, Bruce
View 8 Replies
View Related
Apr 20, 2015
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:
[URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.
View 1 Replies
View Related
Apr 20, 2015
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link: [URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- Query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase]
FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID]
INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId]
WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase]
FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID]
INNER JOIN
[dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID]
WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2
View 15 Replies
View Related
Apr 3, 2006
Hello,
I am trying to extract from some strings like the following strings the number and order them by that number:
Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)
The problem I am having is that I already extracted the number using
Substring([field],[starting position],[lenght])
but the output seems to be in a string format, so the order is not in an ascending order.
Thanks for any suggestions.
View 6 Replies
View Related
Jul 23, 2015
In y sql server table has millions of records available. I don't want to drop the tables.
My requirement is I want to change the column order of an existing table. some tables I am able to saving on design window Like Below image.
Even I had generate a script and using that script trying to execute on Management Studio but unable to saving the new column orders. I am getting the Timeout expired error after couple of minutes. How can we save the orders without dropping the table !
View 13 Replies
View Related
Oct 30, 2000
I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure.
columnA columnB columnC columnD columnE columnF columnG
char char int int int smallint char
it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts.
insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3)
insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2)
These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens.
Thanks
View 1 Replies
View Related
Sep 20, 2001
Hello!
I have two similar servers (hardware) with
SQL Server 2000 (SP1) on Win2000 (SP2).
(sp_helpsort - default)
When I run the same SELECT statement from Query Analyzer on each machine
I get different rows order in result Grid
(the order of rows stays the same for each machine but different for
machine1 and machine2)
SELECT table1.a,
table1.b,
table1.c,
table1.d
FROM table2,
table1,
table3
WHERE table2.nTestDefnId = 1034
AND table2.nDefnId = table1.nDefnId
AND table3.szClinDiscType = 'X'
AND table2.nDiscId = table3.nDiscId
What is a reason for such behavior and how I can fix it?
Thank you in advance,
Elena.
View 1 Replies
View Related
Aug 26, 2015
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.
View 2 Replies
View Related
Nov 5, 2012
I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.
Specifically, something like:
Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"
I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.
(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).
View 6 Replies
View Related
Jun 12, 2007
Hi,
I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table.
In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file.
All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case.
Is this normal??
Thanks
View 8 Replies
View Related
Jan 12, 2005
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted?
this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View 6 Replies
View Related
Apr 15, 2004
the table is like :
ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......
the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.
how can I delete rows with ID in (2 , 3 , 5)?
View 7 Replies
View Related
Aug 15, 2007
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers.
The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made...
So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)?
I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct.
Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152".
FYI, I've lost the debate that such auditing is better handled by the application, not the database server...
Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation?
Thanks
Mike
View 12 Replies
View Related
Jul 21, 2006
How would I copy entire rows within a table and change one value?
insert into user
(user_id,account_id,user_type_cd,name,e_mail_addr,login_failure_cnt,admin_user,primary_user)
select * from pnet_user where account_id='DDD111'
but now I want to change DDD111 to DDD222 on the inserted entries.
Thanks for any help.
View 2 Replies
View Related
