Hi,
I have a form that should show 2 pictures based on table entries.
I want those 2 pictures to be randomly selected based on a database table. So, my table has all the entries, and I want to pull out a random entry that has been approved to display it.
Can someone help me with the sql query?
I can do SELECT VoteId FROM tblVotes WHERE Approved=True..
But how do I make selection a random one that changes every time the user gets another entry?
I have a table which stores phone numbers of a customer in a table.
Say this table is as below
CustomerName - PhoneNumber
Customer 1 - Phone number 1
Customer 2 - Phone number 2
Customer 2 - Phone number 3
Customer 3 - Phone number 4
What would be the best approach to prevent adding another entry against Customer 2. I should be able to add new customers and add multiple phone number against all other Customers. The restriction should be only against Customer 2.
Is there a way I can have all entries in a table automatically in caps once entered by user, import or any other way ?
Thanks a lot in advance.
-PFD
For example some data has entered into a table in a random manner i.e the pk filed value is not in a serial fashion.Is there any table or index that holds the entries of rows into a particular table as entered .
i.e
'some_table' has data like this
3,entry3
2,entry2
4,entry4
1,entry1
I want some DB table or Index that holds data like this about above 'some_table'
row_id .... .... ....
1
2
3
4
here 1 refers to entry of the first column in 'some_table' i.e 3,entry3
and so on...
Hi! I am joining 3 tables in SQL , I am getting the results I want exept it's duplicated. So the resultinmg table fom my stored procedure has 3 rows that have the same bulletin. How do I filter the storedprocedure to output only the rows that don't have duplicate entries for the column 'Bulletin' Thanks.
Here is my stored procedure:PROCEDURE [dbo].[spGetCompBulletins]
@Userid uniqueidentifier OUTPUT,@DisplayName varchar(200)
AS
SELECT *
FROM dbo.UserProfile INNER JOIN
dbo.bulletins ON dbo.UserProfile.UserId = dbo.bulletins.Userid INNER JOINdbo.Associations ON dbo.Associations.BusinessID = dbo.bulletins.Userid WHERE UserProfile.DisplayName=@DisplayName
and Userprofile.Userid = @Userid ORDER BY Bulletins.Bulletin_Date
Return
Hi all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
I have an ASP.Net Web appplication with a Back-End SQL DB. There are 3 Tables; Users, Groups, and GroupMember.
The GroupMember table is used to link Users to Groups and consists of just two fields; userID and GroupID.
Here is a sample of some data:
User1 Group1
User1 Group2
User2 Group2
User3 Group1
User3 Group3
Users can belong to multiple Groups. However, you shouldn't be able to have the same user and group comobination more than once. for example:
User1 Group1
User2 Group2
User1 Group1
I can stop this kind of duplicate data entry by doing a lookup first (using asp.net) to see if the entry already exists but this seems cumbersome.
Is there a simpler way to prevent duplicate entries in a table using sql?
Thanks a lot,
Chris
I have a question on appending and deleting entries in mysql table. This is my sample table.
table name: details
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
[Code] ....
I have numerous entries of same id name belonging to same median number.However,I want to only retain the entries having the longest first and end position and discard the remaining entries
E.g. for id name ="PSK_30s1207681L002" AND median = 5 we have four entries
id_name | model | mode | media| first | end | id | level |
+--------------------+-------+---------+-----+-------+-------+--------+--------+
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14684 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 14838 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15236 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
But I want to retain only the one having longest first and end points ie
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
Here ,since for id name ="PSK_30s1207681L002" & median = N we have only 1 entry I want retain that one.
My final output in mysql table should look like this.
id_name | model | mode | media| first | last | id | level |
| PSK_30s1207681L002 | -1 | 1 | 5 | 14026 | 15423 | 6255 | q |
| PSK_30s1207681L002 | 1 | 1 | N | 14026 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | 3 | 14033 | 15627 | 6255 | q |
| PSK_30s1207681L002 | -1 | 1 | T0 | 15550 | 16050 | 6255 | q |
| PSK_30s1189731L001 | -1 | 1 | 3 | 999 | 7531 | 9808 | c |
| PSK_30s1189731L001 | -1 | 1 | 5 | 6609 | 8257 | 9808 | c |
[Code] ....
Is there anything that I could do to append it?
I am troubling SQL stored procedures. The SP has temp table created to contain data. How can I populate the temp table in the SQL debug mode?
View 1 Replies View RelatedHi,
I have a table with no primary key and i just want to see all the duplicate entries on the basis of two columns. Can anyone suggest me how should i go about it.
Can anyone provide me the syntax for the same?
I have only 1 table say ISSR_TBL and two columns using which i want to delete the duplicate ones. i.e. MIN and MAX.
Please help me out...
Helo everybody,
How can i find a dupplicate entry in one table?
If I wanted to search for Jobs as a particular status (e.g. 0130) and wanted to keep the jobs at this status until it has reached 0500, 0125, or 0900 in it's subsequent status log entry, how can I write the SQL for it to achieve it?
I have the following SQL which searches for the Jobs at 0130, but don't know how to develop it further to search on the requirement above.
------ SQL -------
SELECT
job.job_number,
(SELECT MAX(jsl.job_log_number)
FROM job_status_log jsl
WHERE
job.job_number = jsl.job_number AND
jsl.status_code = '0130') as Last_Early_Warning_Status_Entry
[code].....
In the job_status_log table above, there is a job_log_number field which increments by 1 when there is a new status log entry.
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies View RelatedHi everyone,
I am using this temporary data table which gets cluttered after certain time (table is used for registering data waiting for email confirmation).
Is there a possibility to empty a data table automatically every day (at a certain moment)?
Kind regards,Maxime
The following is the trigger which create a row in the audit table when a single deletion is occurred.
ALTER TRIGGER [dbo].[TRG_Delete_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
[code]....
I am trying to create a trigger which should prevent the bulk deletion. The following is the trigger which I have written, it is preventing the bulk deletion. But the problem is, it is removing the single deletion entries in the audit table. I want audit table to hold back the single deletion entries without allowing the bult deletion
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
[code]....
I got error while updating a table ,duplicate entries and trigger got fired ..so how to debug the trigger to know duplicate record.
View 1 Replies View RelatedHiOur product uses MS-SQL Server 2000. One of our customer has 10installations with each installation stroring data in its own database.Now the customer wants to consolidate these databases into one and wealready have plan for that by consolidating one DB at a time. But firstthey want to find how many unique or duplicate entries they have acrossall the 10 databasesAssumptions:1. All the databases reside on the same server. (This is just anassumption, not the real environment at customer site)2. Databases can not be merged before it is found how many unique orduplicate rows exist.Table under consideration:Message(HashID PK,....)# of rows in Message table in each of databases: 1 MillionHere is my question: How can I find how many unique or duplicateentries they have across all the 10 databases. I easily find uniquerows for two databases with a query like this:SELECT COUNT(A.HasID) FROM db1.dbo.Message A LEFT OUTER JOIN ONdb2.dbo.Message B ON A.HashID = B.HashID WHERE B.HashID IS NULLHow can I do this for 10 databases. This will require factorial of 10queries to solve this problem.I will appreciate if someone can provide hint on this.RegardsAK
View 3 Replies View RelatedI have a table 'Group2Operation' that stores many to many relationsbetween the 'Group' table and the 'Operation' table (each group is haspermission to perform one or more of the available operations)PROBLEM=======I need to prevent duplicate entries being created. e.g. lets say thatin the 'Group2Operation' table a record links the 'editor' group tothe 'publish' operation. Should I prevent an administrator creating aduplicate of that record? (Otherwise deleting that permission willhave to be done twice or more for it to be effective)SOLUTION?=========So far I've done this with a trigger:CREATE TRIGGER Group2OperationDuplicates ON dbo.Group2OperationFOR INSERT, UPDATEAS UPDATE Group2OperationSET NoDuplicate = CONVERT(nvarchar(10),GroupID) + OperationTagThe 'NoDuplicate' unique index column in the Group2Operation tablestores a concatenation of the unique group and operation identifiers.So when an attempt is made to create a record, the trigger is fired.If there is a duplicate, this will mean a duplicate entry in the'NoDuplicate' column. As a result, the INSERT or UPDATE will fail andthe duplication will be prevented.WHAT DO YOU THINK?==================What do you think? Am I going about this in the right way? Is atrigger a good way to do this or should I rely on application logic toprevent duplicates?Any help appreciated by this db novice.John Grist
View 2 Replies View RelatedSomeone ran an update statement multiple times so their are multiple entries in the table. What is the quickest way to track down the multiple entries? I would only want to see where timein and timeoff exist in the table multiple times for the same id. So this would be a duplicate
EntryID -- ID -- timein -- timeoff
1487 11 2015-05-05 16:33:23 2015-05-05 18:45:26
1623 11 2015-05-05 16:33:23 2015-05-05 18:45:26
When I execute the following query several times, I get the same row if there is no new data inserted:
SELECT TOP 1 * FROM TableName
Is there a way to get a random row from the table? Thanks in advance.
Hi,I am in a situation where our developer is on leave (annual leave for a month), and I have to add a control to my website, which is in aspx apges.To start with i have created a table in my SQL database. this table has records with one-liners from various movies, and the movie title. The tabel have 3 columns, i.e. ID, Liners, MTitle.So i want to get random records on the page when ever its refreshed. I am totally non-coder/programmer guy. I have got a SQL statement from the internet " SELECT TOP 1 * FROM <table name> ORDER By NEWID() "So would anyone please help me out with it, as is it correct, how can i apply in the aspx pages. Thank you.
View 2 Replies View RelatedI am using Sql Server 2000.
I have a customer table with fields - CustId, Name, Address, City, StdCode, Phone.
I used to insert entries in this table from an excel file.
One excel file will contain thousands of customer.
In this table combination of StdCode and Phone should not be repeated.
If I do it in my VB.Net coding.then application gets drastically slow.
So I want to write a procedure or trigger for this.
Here what I will do, I will send all records into database then this trigger or procedure will check for
any existing entry of combination of StdCode and phone. If entry exists then this will delete new entry
or will not allow this new entry.
Is this possible to do using Trigger or stored procedure?
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
Can anyone help me on this...
when i select data from table using select statement it takes huge amount of time....The table contains 7 million entries and when i select by mentioning a criteria it takes around 45 secs..The system has 4GB RAM and Dual Processing CPU. The select statement does not contain any grouping and all..
Will it take this much time to retrieve data.?.
The table does include an indexed field,
So can anyone help me on the different things i can do to make the retrieval faster?
Andy
Hello,I have 1000 of records in my table. I wanna select random 100 of them? How?regards
View 3 Replies View RelatedHello.
I need to select a random record from TABLE. It might look easy with using RAND() function, but the tricky part is that ID's which are the PRIMARY KEY, were assigned as a random number.
So right now ID's in that TABLE look some thing like that: -18745, 45809, 129, -5890023, 487910943, -209, etc...
If any one have any ideas please respond.
Thanks in advance.
Hello,
I need to write a select statement where I need to select the record randomely. Is there any function to do that? What's the best way to do that? Please help...
Thanks in a million.
Sarika
Hi all!
I want to fill a table with random values. In each line should be a different value - "independet" from the value the row above.
what i tried didnt work, it always produced the same value.
Maybe you have an idea
Thankx in advance and greetings from Vienna
Landau
I Got the Sql query "Select Top 1 * from Books order by NewId()"........ for select Random row in a table .. any one can explain Above Query
Regards,
S.sajan
Hello Everyone,
I want to get a random value from a table only once a day, not on every page load.
With this SQL I can get a random value and everytime it gives me new value....
SELECT TOP 1 PRODUCTID
FROM PRODUCT
ORDER BY NEWID()
How the above query can be changed or there can be some sort of trigger set that same value for 24 hours....and it changes after that ?
Hello,
Anyone have any suggestions on creating a query that will randomly select records from a table, but not use those records again. I have some code that does it, but it uses the same fields over again, and also throws in some blank records that I did not specify in the query. I am creating a test engine that has to randomly ask questions.
hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).
Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field. I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database.
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.
My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
TIA
We have a SQL Server 2008R2 system that has heavy usage to one specific table. I have tuned basically all I can as far as making sure SQL Statements are using good indexes. From time to time a group of folks will log into Mgt Studio and run SQL Statements like this, leave the query open and once in a while it will cause blocking to other SQL running our online system
The query is like this: select ID,* from tablename with (nolock) where ID like 'MSPRYy%'
The results come back within less than 1 second. However, they leave this window open which is what causes this to be a HEAD BLOCKER and blocks other SQL Statements from running.