SQL To Get Latest Result And Pull Only One Line Fo
Feb 7, 2008
I found an answer on your web site but it was for SQL 2005 and I am using SQL 2000. The question was posted on 2/7/08 from genelam ---- SQL to get latest result from each ID. Below is my sql:
from Protean.dbo.poPurchaseOrder AS po
inner join protean.dbo.poline poln
on poln.parentobjectid = po.objectid
inner join protean.dbo.podelivery podel
on podel.parentobjectid = poln.objectid
and po.posite= 'LIG'
and podel.OrdRescSite = 'LIG'
order by podel.OrdResc
SQL would return the data below:
LastEditDate OrderResc PO
I need the SQL to give the result below by pulling only one PO for that OrderResc number with the latest and greatest LastEditDate and the result should be below:
LastEditDate OrderResc PO
02/04/2008 130-100218 PG00252
12/10/2005 130-100214 PG98612
This should be easy but I can't get an solution from anyone ---- please some HELP! Thanks, Deb
I have the following table called BADSANTA: varchar(30) datetime NAME WHENBAD OJ Simpson 2007-1-12 xx:xx:xx:xxx OJ Simpson 2007-4-2 xx:xx:xx:xxx Monica Lewinsky 2006-7-4 xx:xx:xx:xxx Monica Lewinsky 2006-10-31 xx:xx:xx:xxx Bill Clinton 2006-7-4 xx:xx:xx:xxx Bill Clinton 2006-10-31 xx:xx:xx:xxx Bart Simpson 2006-11-2 xx:xx:xx:xxx Bart Simpson 2006-2-25 xx:xx:xx:xxx Bart Simpson 2005-07-27 xx:xx:xx:xxx
I want the records of the persons latest WHENBAD date. It should return one recordset for each person.
this will be my first post to sqlteam.com. I've been trying to work out this problem for awhile, and figured I really need help. I have a set of data that shows the results of a number of tests that I have been doing on a software. Each test I have done records the test_ID, test_Result, and the date_Tested. An example of the columns would be like this:
Is there a way I can query this? I've tried using "distinct test_ID" but it would still show the repeated test_ID due to the date_Tested not being the same.. also Group By will not work. I'm not sure what to do here. Any ideas anyone?
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
Hi, for some AP issue, the file I upload must be without the line feed/carriage return in the last line. for example:
original fixed-length file (exported from SSIS) line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90 4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line. line NO DATA 1 AA123456 50 60 2 BB123456 30 40 3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
I need the Trend line for the following data in Line chart they are the following data. The following are the graph are my output and i need the trend line for these Key_gap value.
This is the link [URL] ....
I need the same trend line for the Bar-Chart in SSRS 2005.
I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph? Let me know if you need a better explanation. Thanks in advance!
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
We have a line graph which plots the actual data points (x,y), everything is working fine with this graph. Now we need to add a trend line to this existing graph after going thro. the articles we came to know that there is no direct option in SSRS to draw a trend line. So we need to calculate the trend values ourselves which we need to plot as atrend line. This trend line is similar to the trend line which comes in Excel chart, do anyone know how to calculate the trend values from the actual data points. We got through several formulas, but were not clear, have anyone tried out exactly the same, if so please help us out by providing an example to calculate the trend values.
I have a line graph which shows positive and negative values. Is it possible to have the line one color when its negative and another when its positive?
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
Hi If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;" But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;" Then i get Images(6)Images(1)Images(1) But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this Images(1)Images(1) But i cant get it to work.
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?
Ex:
TransactionID | Customer ID | TrasanctionDate
1 | 2 | 9/3/2007 12:00:00 AM 2 | 3 | 9/4/2007 12:00:00 AM 3 | 2 | 9/5/2007 12:00:00 AM 4 | 2 | 9/6/2007 12:00:00 AM
Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2
4 | 2 | 9/6/2007 12:00:00 AM
Any help will be greatly appreciated. Thanks in advance!
We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.
With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.
I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)
I have table has column called [last modified date] it has time too , i need to get the [latest modified date] for particular ID , there are many updates to the ID and i just need the latest date.
