Hello.
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.
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 RelatedHello,
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 Have the following situation:
CREATE TABLE First_Table (id INTEGER IDENTITY(1,1) NOT NULL,
titre VARCHAR(50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
genre VARCHAR(20) NOT NULL,
resume TEXT,
codePays VARCHAR(4),
CONSTRAINT PKFilm PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
I'ld like fill in this tables records inserting in the column id values I got in the one other table. In Oracle it is possible to do it using sourceTable.nextval where sourceTable is created as: CREATE SEQUENCE sourceTable;
How can I do it in MS SQL or Transact-sql?
Thanx all
Hello everyone, I have a query problem.I'll put it like this. There is a 'publishers' table, and there is a'titles' table. Publishers publish titles (of course). Now I want to make aquery (in MS SQL Server) that would return the last title published by everyof the publishers. Quite clear situation. But I can't make it work.If I use inner join (which I should, because I need data from both tables)then I get a result showing all publishers and all titles. What I want toget is all publishers, and only their last title, so I don't have more thanone line for the same publisher, and this line should contain publisherdetails and last title details.I tried using DISTINCT, but it works on a whole resultant row rather then acolumn, and since rows are all distnict (because they also contain columnsfrom titles) this didn't help me.What I can do is (in my application) first get a list of publishers, andthen loop through them selecting only the last title belonging to eachpublisher. I want to see if there is a way to accomplish the same thing withan SQL query (or maybe a stored procedure, view, or whatever). Anything ispossible, as long as it stays within SQL server and doesn't rely on theclient application.Of course, both 'publishers' and 'titles' tables have a primary key('publisherID', and 'titleID'), and 'titles' has a 'publisherID' columnwhich relates titles with publishers.Help :)
View 4 Replies View RelatedI have a table (Billing Table) having multiple records for people having one record per person per each month.
How to get a list of the guys having record just for one month (say feb) and doesn't have any other months.
Im trying to write a statement that only selects one of the possible rows that could be given.
For example:
Car ID: Colours Available:
1 Blue
1 Green
1 Black
I want the statement to return:
Car ID: Colours Available:
1 Blue(Random Selection)
Is this possible?
Thanks
hi again,
i just want to ask if how can i randomly select 5 distinct records from a table w/ a hundreds of records everytime i exec a stored procedure??
thanks
How would I create a statement that would select 10 random records from the SQL DB?
View 5 Replies View RelatedI have three tables that are joined together to retrieve a piece of data.Table One has the columns pid1(PK), idTable Two has the columns pid1, qid1, idTable three has columns qid1, question, answerHere is my sql statement:SELECT Table1.pid1, Table2.qid1, Table3.Question FROM Table1 JOIN Table2 ON Table1.pid1=Table2.pid1 JOIN Table3 ON Table2.qid1=Table3.qid1 WHERE Table1.pid=...(N)I put an elipsis because I'm stuck on how to proceed from here. See I need to randomly select a value from column pid1 in table1 then use this value to replace N in the sql statement, then have the statement run and randomly return exactly one piece of data instead.Is this possible? How can you randomly get one piece of data from joining three table without a WHERE CLAUSE.
View 1 Replies View RelatedHello,
I need to select records by random order everytime user go to that page. QuestionID is the uniq field in this table.
SELECT * FROM QuestionMaster.
I tried to use the following statement to get the random records but I can't get the value in the variable out of this statement that I can use in my above select statement. Please HELP.
SELECT Convert(int,RAND()*97)
Thanks in a million.
Sarika
hello members
i want to no that weathere there is any procedure to select come random records from the database
for eg i want to select 10 students out of 100 randomly
is there any query for this in SQL server 200
I am using this select statement to radomly display a recordSelectCommand="SELECT TOP 1 * FROM [TBL_Example] ORDER BY NEWID()I need to, however, flag this record, to determine if it has already been previously randomly selected, and won't take part in future random selections.I will need to add a where clause to the above, but what I am unsure of is what I should do for the insert statement. I guess I could figure this out on my own as well if I could determine a means to prgramatically store my PK from the above record in session.Any ideas?
View 1 Replies View RelatedI'm familiar with querying for a random record, however I can't seem to do it using a parameter driven TOP value.
Here's what I've tried in a stored procedure:
(
@Count int = 1
)
declare @sql varchar(1000)
set @sql = 'SELECT DISTINCT TOP ' + convert(varchar, @Count) + ' * FROM RandomisedView'
The view I am using looks like:
SELECT TOP 100 PERCENT dbo.TableName.* ORDER BY NEWID()
The stored procedure does not seem to cause the View to reorder the records.
I need to be able to select X random records. Any help would be appriciated.
I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?
View 1 Replies View RelatedI need to assign a random number between 0 and 1 to all eligible cases of cancer.
I have a file of records like:
patientid
tumid
site
How can I assign a random number to each record?
Hi i was wanting to know how to select a record in a gridview. I have a gridview with firsname and lastname. I currently have select command on it but don't want it. I want to be able to select first name or last name and have it take me to that record on the database.
View 3 Replies View RelatedI'm trying to add a next and previous button to a popup window I have on a photo gallery -
Having trouble with the sql
I know I would have 2 select statements that would each return 1 result set.
I know how to get the top ( and bottom by ordering descending and doing Select Top 1)
But how would I get the next one up!
I would hate returning the entire image section and perform the calcuation in the business tier :(
I have one table like this:
Code:
CREATE TABLE DlIndexTable
(SessionStartTime DATETIME NOT NULL PRIMARY KEY, SchemaID INTEGER NOT NULL,
DLBaseRate REAL NOT NULL)
and one like so:
Code:
CREATE TABLE DlTextDataTable (
SessionStartTime DATETIME NOT NULL REFERENCES DlIndexTable,
ChTimestamp FLOAT NOT NULL, Channel01data VARCHAR (255),
Channel02data VARCHAR (255), ... , Channel16data VARCHAR (255),
CONSTRAINT TxtDatPriKey PRIMARY KEY (SessionStartTime, ChTimestamp))
I want to get some combined data from both tables, so right now I am joining them at the SessionStartTime column, which is a primary key in the first and a foreign key in the second table, something like this:
Code:
SELECT DlIndexTable.SessionStartTime, DlTextDataTable.Channel01data
FROM DlIndexTable
LEFT JOIN DlTextDataTable
ON DlIndexTable.SessionStartTime = DlTextDataTable.SessionStartTime
WHERE DlIndexTable.SessionStartTime BETWEEN '2006-10-13 16:40:08.790' AND '2012-03-01 17:54:30.930'
ORDER BY DlIndexTable.SessionStartTime, DlTextDataTable.ChTimestamp
The trouble is that this query, exactly as requested, gives me all the entries from the second table matching the first, while I really would like to pick just one row (preferably, the first chronologically - by ChTimestamp) so that the first column (SessionStartTime) has distinct entries in the resulting table. What would be the simplest way of doing that? Performance is not a big priority over simplicity since the first table could have only a few hundred rows (maybe a couple of thousand), while the second will be real tiny.
I’m writing a document management system. The documents themselves are created from the contents of a database. The database is SQL Server.
The database contains a table, like so:
ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name
Initially, the user will create a “V0.1” document. So the data would look something like
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 0
MinorVersion = 1
Name = “My Document”
Thereafter, the user can create new versions as “0.2”, “0.3”, etc., or “1.0”, “1.1”, “2.0”, etc.
For example, a “2.1” document would be stored as:
ClientID = 1
ProjectID = 1
DocumentID = 1
MajorVersion = 2
MinorVersion = 1
Name = “My Document”
The earlier versions will still exist on the database, but the latest version will be 2.1.
There may be several different documents, with different DocumentID’s (e.g. DocumentID = “1”, DocumentID = “2”), etc., and each of these documents may have many versions.
I’m trying to write a query to display a list of documents showing ClientID, ProjectID, DocumentID, MinorVersion, MajorVersion, Name… but the list should only display the latest version of each document.
So, if the database contained the following records:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,0,1,My Document
1,1,1,0,2,My Document
1,1,1,0,3,My Document
1,1,1,1,0,My Document
1,1,1,2,0,My Document
1,1,1,2,1,My Document
1,1,2,0,1,My Second Document
1,1,2,0,2,My Second Document
1,1,2,0,3,My Second Document
My query should return:
ClientID, ProjectID, DocumentID, MajorVersion, MinorVersion, Name
1,1,1,2,1,My Document
1,1,2,0,3,My Document
… where 2.1 is the latest version of Document 1 and 0.3 is the latest version of Document 2.how to do it.
MSSQL2000I have a table that contains customer transactionsCustomerIDTransactionTransactionDate....I need to select the most recent record that matches a specific CustomerID.I am fairly new to SQL, could someone provide a sample select statement.TIATim Morrison-- Tim Morrison--------------------------------------------------------------------------------Vehicle Web Studio - The easiest way to create and maintain your vehicle related website.http://www.vehiclewebstudio.com
View 3 Replies View RelatedI have a stored procedure (below), that is supposeto get a Reg Number from a table, (Reg_Number), insuch a way that every time the stored procedure is called,it will get a different reg number, even if the storedprocedure is called simultaneously from two differentplaces,However it is not working that way.If two different users access a function in thereVB program at the same time, the two different userswill get the same reg number.I have looked at the stored procedure, it looks foolproof,yet it is not working that way.Thanks in Advance,Laurence NuttallProgrammer Analyst IIIUCLA - Division of Continuing Education'---------------------------------------------------------------------------Here it is:CREATE PROCEDURE sp_GetNextRegNum@newRegNum char(6) = NULL OUTPUTASLABEL_GET_ANOTHER_REG:Select @newRegNum =(select min(Reg) from reg_number)IF Exists (select Reg from reg_number where reg = @newRegNum )BeginDelete from reg_number where reg = @newRegNumIF @@Error <> 0BeginGoto LABEL_GET_ANOTHER_REGEnd--EndifEndELSEGoTo LABEL_GET_ANOTHER_REG--EndifGO
View 6 Replies View Related
This is my first post so I hope I'm doing it to the right forum. I need to compare a LastUpdated time to Start and End Time fields to get the "Due" Time for a given order. Can someone give me the correct SQL? (the example below should result in 6:00:00 AM "Due" time.) All are DATETIME fields. Thank you.
LastUpdated StartTime EndTime DueTime
5/29/2007 12:04:32 AM 2:00:00 AM 11:30:00 AM 3:30:00 PM
11:30:00 AM 5:00:00 PM 9:00:00 PM 5:00:00 PM 2:00:00 AM 6:00:00 AM
I'm no SQL whizz yet but I'm learning hard, and need to get some information from our DB rather urgently so have resorted to this fantastic forum, only I can't find what I'm looking for.
Basically I'm selecting a whole load of entries that have a (admission)date field after 2001, but I only want to return the Earliest (admission) for each (patients number).
Here is the script I have created to select all the data, but how can I limit the results to just the earliest (admission date) for each (patient).
SELECT
Admission_Year, Admission_Month, Age_On_Admission, [Length of stay(continuing)], [Patient's Number], [Cons epis seq no], Sex, [Main Primary Pas Diag], [Date of Death], [Epi duration], [OP Code1], [Admission date], [Date of Death] - [Admission date] AS [days before death],[Intended Management]
FROM dbo.Admissions
WHERE (Admission_Year > 2001) AND (Age_On_Admission > '64') AND ([Intended Management] = 'inpatient') AND ([Date of Death] IS NULL)
I would really appreciate it if anyone can help with this, I'm sorry I can't really contribute to this forum as an SQL expert as .net is really my forte and I usually spend my time contributing to the asp.net forums. :)
Hi
I'm sure this is an easy problem but my brain is fried today...however how do I do the following:
I have a two column table. One is a key field where duplicates can arise and the other is a datetime field. So you might have some records looking like this:
1231999-06-14 12:17:11.000
1231999-06-14 12:17:31.310
1231999-06-14 12:17:31.000
1231999-06-14 12:22:56.000
1231999-06-14 12:22:58.000
8901999-06-15 10:00:18.000
8901999-06-15 10:03:30.340
8901999-06-15 10:03:30.000
8901999-06-15 10:03:40.000
OK, how do I get the top 1 of each key so that I get a subset of records looking like the following:
1231999-06-14 12:17:11.000
8901999-06-15 10:00:18.000
Thanks in advance
Bazza
I have the following query I am using to identify duplicate records in one of my database tables:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences
FROM ChapterMembers
GROUP BY memberID
HAVING ( COUNT(memberID) > 1 )
Executing the above proc returns 4079 records...
Now, I would also like to know the ChapterID for each member with a duplicate record. ChapterID is also stored in the ChapterMembers Table...
I tried running the following procedure:
Code:
SELECT memberID,
COUNT(memberID) AS NumOccurrences, chapterID
FROM ChapterMembers
GROUP BY memberID, chapterID
HAVING ( COUNT(memberID) > 1 )
But zero results are returned ...
The ultimate goal here is to identify duplicate records where one of their chapterID's = '81017' and to delete that record from the database.
Anyone have any ideas what I am doing wrong? Also, any suggestions for removing the records would be appreciated.
Thanks,
Jandrews
The situation is that we have resources (trucks) that perform shifts. Shifts consists of actions. A resource can perform multiple shifts.
For every resource we want to find the record that:
- Is 'younger' than the last realized action.
- Has actionkind pickup, deliver or clean
I have constructed a solution with CTE and row_number but I was curious if there would be other alternatives. The fact that I'm joining a CTE onto itself and subject the outcome to a partition makes me think there are sharper ways.
Note that the action id in the data below is also sorted but in practice this need not be the case. The sorting key is prevalent.
output of the query is
id_action id_resource actionKindCode
4665 4 clean
34540 96 pickup
24000 901 clean
declare @mytable table (
id_action int,
id_shift int,
[code]....
Hi,
Is it possible to select a record by just the date part of the data type TIMESTAMP?
date_time
2007-04-20 16:27:01
For example, a query to select all records added today.
Thanks very much,
dai.hop
Good Morning,I have a person table with personID. I have a personRate table withpersonID, rateID, and effectiveDate.I need to select fields from personRate, but I want the fields from theproper record.I need the one child record that has the most current date of the largestrateID.For example a person may have many rate records. I need the record that hasthe most current date of the largest rateID they have. Does that makesense?I am making a view that has data from both tables. I need to display themost current rate info.Any ideas? TIA ~ CK
View 4 Replies View RelatedI have created the following SQL snippet that is a very simple mock-up illustrating the problem (I hope!) that I am facing:
-- create table
if object_id('tempdb..#tmpdelnotes') is not null
drop table #tmpdelnotes
create table #tmpdelnotes(
DelNote int identity (1,1) ,
DelDate date not null,
Item int not null,
Customer int not null)
[code]...
What I need to retrieve is a unique list of item numbers with information about the latest (DelDate) delivery note. The "Clumsy workaround" works, but is not very pretty when doing multiple table joins. Is it really necessary to use a derived table for this kind of query? Window functions can only exist in the SELECT and ORDER BY clauses, which is understandable since the calculations take place (I would guess) after the aggregations in the HAVING clause.
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
Hi,I have a stored procedure that has to extract the child records forparticular parent records.The issue is that in some cases I do not want to extract all the childrecords only a certain number of them.Firstly I identify all the parent records that have the requird numberof child records and insert them into the result table.insert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select t1.BatchNumber,t1.EntryRecordID,t1.LN,t1.AdditionalQualCritPassedfrom(select BatchNumber,RecordType,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Temp) as t1inner join(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) = max(TotalNumbers)) as t2on t1.BatchNumber = t2.BatchNumberand t1.RecordType = t2.RecordTypeand t1.EntryRecordID = t2.EntryRecordID)then insert the remaining records into a temp table where the number ofrecords required does not equal the total number of child records, andthenloop through each record manipulating the ROWNUMBER to only selectthe number of child records needed.insert into @t_QualificationMismatchedAllocs([BatchNumber],[RecordType],[EntryRecordID],[AssignedNumbers],[TotalNumbers])(select BatchNumber,RecordType,EntryRecordID,Count(*) as AssignedNumbers,max(TotalNumbers) as TotalNumbersfrom t_AuditQualifiedNumberExtractDetails_Tempgroup by BatchNumber, RecordType, EntryRecordIDhaving count(*) <max(TotalNumbers))SELECT @QualificationMismatched_RowCnt = 1SELECT @MaxQualificationMismatched = (select count(*) from@t_QualificationMismatchedAllocs)while @QualificationMismatched_RowCnt <= @MaxQualificationMismatchedbegin--## Get Prize Draw to extract numbers forselect @RecordType = RecordType,@EntryRecordID = EntryRecordID,@AssignedNumbers = AssignedNumbers,@TotalNumbers = TotalNumbersfrom @t_QualificationMismatchedAllocswhere QualMismatchedAllocsRowNum = @QualificationMismatched_RowCntSET ROWCOUNT @TotalNumbersinsert into t_AuditQualifiedNumberExtractDetails(BatchNumber,EntryRecordID,LN,AdditionalQualCritPassed)(select BatchNumber,EntryRecordID,LN,AdditionalQualCritPassedfrom t_AuditQualifiedNumberExtractDetails_Tempwhere RecordType = @RecordTypeand EntryRecordID = @EntryRecordID)SET @QualificationMismatched_RowCnt =QualificationMismatched_RowCnt + 1SET ROWCOUNT 0endIs there a better methodology for doing this .....Is the use of a table variable here incorrect ?Should I be using a temporary table or indexed table if there are alarge number of parent records where the child records required doesnot match the total number of child records ?
View 2 Replies View Related
This is a simple one, and I know that it has to be fairly common, but I just can't figure out an elegant way to do it. I have a table with the following fields:
OrderID (FK, not unique)
InstallationDate (Datetime)
CreateDtTm (Datetime)
There is no PK or Unique ID on this table, though an combo of OrderID and CreateDtTm would ostensibly be a unique identifier.
For each OrderID, I need to pull the InstallationDate that was created most recently (based on CreateDtTm). Here's what I've got so far, and it works, but man is it ugly:
SELECT a.OrderID, InstallationDate
FROM ScheduleDateLog a
INNER JOIN
(SELECT OrderID, max(convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)) as TopRecord
FROM ScheduleDateLog GROUP BY OrderID) as b
ON convert(varchar(10),CreateDtTm,102)+'||' +convert(varchar(10), InstallationDate,102)=b.TopRecord
AND a.OrderID = b.OrderID