If I wanted to run a query on any table and in the recordset that is returned have an 'id' field (or whatever) with the record id of that record, how would I do this?
I'm thinking something like
Select field1, field2, recordNumber // derived somehow - not an actual field
from table
where the result woule be:
field1 field2 1
field1 field2 2
field1 field2 3
field1 field2 4
field1 field2 5
...
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from (
select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data
where
dm_type = 'prime' and
dm_closedate <= '31-dec-2007' and
dm_closedate >= '1-dec-2007' and
program = 'aads'
group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from
(select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days
from dm_data dm, ProgramXref px
where
px.Program_Name = 'aads'
and dm.Program_Id = px.Program_Id
and dm.dm_type = 'prime'
and dm.dm_closedate <= '31-dec-2007'
and dm.dm_closedate >= '1-dec-2007'
group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
Thanks,
Lee
If I selected the data below, how could I write a query to only keep the latest record. I added a "FileDate" column to label the last record (FileDate 200807)I want to display.
LeaseIDOpID Year MonthMCFFileDate
208801407982006 107660200807
208801407982006 107680200801
208801407982006 107784200704
I have created a sample Table with inserting below data,
ID Subject CreatedDate
1 Test 2015-09-20 22:59:07.373
2 Test 2015-09-21 09:16:58.290
3 Test 2015-09-21 09:18:17.500
I am loading a dropdown with Subject. The dropdown list shows the 3 items with same subject name since i have three records with same subject. I need a SQL select query for - if subject 'Test' is selected from the dropdown, need to be able to pull its corresponding associated ID from the table based on CreatedDate?
I've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206 | 3
201 | 5
204 | 3
203 | 1
202 | 8
205 | 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206 | 5/6/2008 | 300 | 6 | 3 | 3
201 | 5/4/2008 | 500 | 9 | 5 | 4
204 | 5/3/2008 | 800 | 4 | 3 | 1
203 | 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to show the query?
I had a query and i need to insert record in table and also want to select output of query.
Actually I need to insert and show data in same query.
;with cte as (
select id , status
from tbl_main
where id = 15555
)
insert into testinsert (id , status)
select * from cte
Good day!
I just can't figure out how I can display only the top record for the duplicate records in my table.
Example:
Table1
Code Date
01 10/1/05
01 10/2/05
01 10/3/05
02 9/9/05
02 9/9/05
02 9/10/05
My desired result would be:
Table1
Code Date
01 10/1/05
02 9/9/05
Thanks.
Hi,
We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.
If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.
Thanks,
Sarah
The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.
This is running on a Websphere Application Server v6.1.
Code snippet - getting the record thru JDBC:
DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();
// wsCon.setAutoCommit(false); //have tried with and without this flag - same results
Statements stmt = wsCon.createStatement();
String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);
if(rs.next()){
System.out.println(rs.getString("lastName"));
}
if (rs != null){
rs.close();
}
if (stmt != null) {
stmt.close();
}
if (wsCon != null) {
wsCon.close();
}
I've read some articles on generating tag clouds but so far only one actually showed a query. And the query was on a simple database with a few tags. In our database we have records that can have literally hundreds of unique tags. So, I'm wondering how to get a realistic result set of the top tags? I can't just do a group by and select the top 30 or 50 because that won't necessarily get me through the alphabet. I'd like to at least have as many letters from the alphabet in the tags as possible like (ant, cat, dog, horse, lion, seal, zebra) but if I do the top 30 it may cut off around the letter "c". Know what I mean? So, how can I generate an accurate tag cloud query that uses all available letters from the alphabet as starting letters for the tags? Thanks.
View 5 Replies View RelatedHi everyone,
I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.
Say i am executing this query
SELECT NAME,AGE,ADDRESS FROM MEMBER which gives
NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON
Now how can i make it come out like this
SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON
See the first column is a auto incrementing number 1,2,3.
How can I write a SQL QUERY that outputs a auto incrementing number.
Thank you
Have nice day
I have a table named "Persons" which has the following columns [ Name ,Age, City]. I have a Windows app which has 3 textboxes for each column. My requirement is when I enter only name and enter the search button, I need result similar to this query.
select * from persons where name like '@tbname%'
when I enter only age and enter the search button, I need result similar to this query.
select * from persons where age =@tbage
when I enter name and city, I need result similar to this query.
select * from persons where name like '@tbname%' and city like '@tbcity%'
If I don't enter any fields and enter the serach button, I need result similar to this query.
select * from persons for these 3 fields I might give any combination as above. But how can I have a single query with all these combinations. I want a single query capable of doing this. I cannot have query like this, select * from persons where name like 'tbname%' and city like 'tbcity%' and age =@age
Because if I give only name, city is considered as null nad age is considered as 0 and I get empty result. I cannot have Stored proc and I cannot do much in C# like using "selectqueryBuilder" etc. I need pure SQL query.
I am developing a datagrid which will display a summary "report" of sales revenue grouped by a particular field. There will also be a drill-down on this field that will display in more detail the table of data that was used for the summary.
Because the user is selecting multiple criteria under which to run the search, I have created a dynamic query that gathers all the necessary data I need for both the drill down and the summary based on the variable parameters of the user. I've optimized the query that gathers the detailed data (taken from 7 tables) to execute far under a second.
So, that said, here's my question: It seems the most efficient way to get the summary data is to run it against the datatable (which gathers the detailed data) created by the dynamic query and just pass them both into a dataset. Can this be done? Originally, I was calling 2 separate sprocs in my application - one for the detailed data and another for the summary data using a "group by" on the table that was produced by the dynamic query (using "INTO TABLE" in the SELECT statement of the dynamic query). With this method, I received an access exception by asp.net. for the table created by the dynamic query so have dropped the "INTO TABLE", not to mention it increased the performance considerably NOT using the table.
Any suggestion on the most efficient/optimal method to use the data generated from the dynamic query sproc for the summary data? I am trying desparately to avoid running the dynamic query again just for the summary data.
I am a total newbie, so would appreciate any feedback. Also, would someone please tell me the datetime stamp function to print so that I can see how many milliseconds it takes to execute stored procedures?
TIA!
Sandy
I didn't work with XML before and so I'm posting this question on how we can generate serialized XML. I have the following table -
DECLARE @Population TABLE (CountryId INT IDENTITY(1,1), CountryName VARCHAR(15), StateName VARCHAR(20), PopulationCount INT)
INSERT INTO @Population (CountryName, StateName, PopulationCount)
VALUES ('USA','California',300000),
('USA','Chicago',500000),
('Australia','Queensland',550000),
[Code] ....
Please note that I can have another country with 10 states or 30 states, so State Name is dynamic. Can this be done in SQL ? or we have to use .NET ?
SQLServer2014
-12.0.2000.8(X64)
I would like to exec a select statement in VB/C++ to return first 100 records? What is the SQL statement should be?
Thanks,
Sam
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?
Hi
Here is my example: I have the following table
ID Name Phone
--- ---- -----
1 John 1234567
1 John H.
2 Dave 9876543
2 Dave Smith
I want to select only one record of each ID, the output should be
ID Name Phone
--- ---- -----
1 John 1234567
2 Dave 9876543
It doesn't matter which record to select, but I need just a single complete record. I can't use select distinct ID because it will not show the other fieldnames.
Thanks
I have a sql query that looks for values in a few different databases....
is there a way to select the last record in the table b/c I am pulling the hours worked on jobs and one person may have 3 job titles but i want it to show the balance of hours under the most recent job... Instead all the jobs are showing the same value even though only one of them had those hours under it. I think a way to get around this is to select the last value b/c that is under the job title most recently worked and they are in order by timesheets which can be ordered by date...any ideas?
I have view with Patients name and Appointment table where I save those patients. How to create store procedure if I would like to select one patient record based on Patient_Id value?
View 3 Replies View RelatedHai frendz,
I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-
"select top 1 EID, Salary from Employee ORDER BY Salary DESC"
Now I need to write a query which selects the second highest salary.
So how to achieve this?..
thanx
hi
I want to lock the record on select query so that no other user can update this record, is it possible and i want to unlock record when use stop view this record.
i have two tables A and B. relation is one to many for A. i want to select from A only if there are more than two records of A in table B and also checking some condition in table B. if the question's not very clear please let me know.
View 1 Replies View Relatedhow to select record from the table where the data between a range. example between 2/16/2005 and 12/16/2005. the data record in the table formated like this ( 2/16/2005 11:44:38 PM). help me with some sql code, thanks
View 2 Replies View RelatedHi,
I need to select first record from a table.
This can be comfortably achieved by usibg set rowcount 1.
I need to do this with out using rowcount.
This is urgent.
Thanks
Krishna
Hi!
I need to select a spesific record using the recordkey and then select the previous and the next record as well. (which leaves me with a recordset containing three records)
Is this possible?
hi friends,
I am having a table with student marks in that i need to select a student who is the 5 th rank based on total marks obtained by the student.how can write sql for that.
name, totmarks
-----------------
kumar 145
ravi 300
jude 189
geeetha 320
rajesh 251
guru 190
ramesh 99
----------------------
i am having records like this.pl help me. thank u
regards
raj
Hello, everyone:
I have two tables like:
Table A
IDCodeANumA
1D1289
2C4300
3C9409
Table B
IDCOdeBNumB
1D3266
2C4300
3C7101
How to find out the records that is in Table A not in Table B, and in Table B not in Table A? That means C4/300 should not be selected.
Thanks
ZYT
The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.
Any suggestion will be appreciated.
ZYT
Every hour we capture some values from our factory (position of pumps, valves, ...) in our sql server 2000 db.
Normally 1 record is added to the db.
00:00:00
01:00:00
02:00:00
...
21:00:00
22:00:00
23:00:00
All of these values are displayed in an Excel sheet. One sheet contains all the data from one month.
I noticed a problem last week when 2 records were added: first one at 19:00:00 and the second one at 19:00:01
We only want to keep the most recent record (19:00:01) in a situation like this but I can't seem to work out an SQL-statement :o
This is what we have know. It used to work fine untill we had 2 record being added instead of one.
SELECT TimeOfCapture, Value1, Value2, Value3
FROM FactoryTable
WHERE (MONTH(TimeOfCapture) = MONTH(GETDATE())) AND (YEAR(TimeOfCapture) = YEAR(GETDATE()))
Hi everyone,
I am having a header table and details table and I want to display first 2 records against each header from details, whatever number of records are there in details against each header.
Example :
=======
Details table is as follows
HeaderID DetailsID
1 1
1 2
1 3
2 4
3 5
3 6
3 7
3 8
output should be:
TransDate SupplierName HeaderID DetailsID
1/1/2000 abc 1 1
1/1/2000 dsds 1 2
2/3/2003 fgd 2 4
2/4/2005 sdsd 3 5
1/1/2006 fgfdg 3 6
I am using the following query
SELECT H.TransDate, H.SupplierName, D.DetailsID FROM
Header H, Details D
WHERE H.HeaderID = D.DetailsID
AND D.DetailsID IN (SELECT TOP 2 DetailsID FROM Details WHERE HeaderID = H.HeaderID)
As I am dealing with very huge data it is taking very long time to execute.
Is there any better way to accomplish the task?
Thanks.
Riyaz
im trying to get the earliest record. data is below
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332/ 20090106<---- / 20081219/ 20090106
2332/ 20090323/ 20081219/ 20090323
2332/ 20090413/ 20081219/ 20090413
2332/ 20090507/ 20081219/ 20090507
because the bankruptcy_date date are all equal i would need to pull one record with the earliest date from the doc_received_date. The date with the arrow is the record i want. So basically i would like to show this result
note_id/ doc_received_date/ bankruptcy_date/ sp_recorded_date
2332 20090106 20081219 20090106
query to show last record/Partner or PartnerName.
select 'PURCHASE' as EntityName, d.PartnerName, h.*
from (
select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId
from DataImport.PurchaseHistory
group by DistributorId, StartDate, EndDate
) h
inner join Partner d on d.PartnerId = h.DistributorId
where d.Active = 1
order by DistributorId, StartDate desc, EndDate desc
I am wondering how to select the Max date within a table where a timestamp is within 2 second of the other
IDTimestamp
1NULL
209/17/2012 11:04:32
309/17/2012 11:05:44
409/17/2012 11:05:45
So I need a query to return
IDTimestamp
409/17/2012 11:05:45
as 11:05:45 is the max of 09/17/2012 11:05:44 and 09/17/2012 11:05:45 and they are within 2 seconds of eachother.
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
TABLE1 info
ID1 fname1 lname1 DOB1
ID2 fname2 lname2 DOB2
ID3 fname3 lname3 DOB3
TABLE2 info
ID1 Visit2
ID1 Visit3
ID1 Visit1
ID1 Visit4
ID2 Visit1
ID2 Visit2
ID3 Visit1
I need a view or SP to return the following:
ID1 fname1 lname1 dob1 visit4
ID2 fname2 lname2 dob2 visit2
ID3 fname3 lname3 dob3 visit1
It seems like it should be a smiple process, only I just can't get the syntax to work.... Any Help would be GREAT!
thx