|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Max Row Dateclear about what I need. I need to get the max row date from the following query. There is a date field in rcompl.date_time. There can be several rows with matching hole_name field but with different dates. I need only the latest of these rows. Below is a sample of data, problem is that the 'duplicate data' is for the hole_name field only. But I need to include all the data. I have simplified the query for the example. Sample Data: id hole_name Mtime 156140 2a000020x1 2006-08-02 19:18:34.000 156141 2a000021x1 2006-08-02 19:19:45.000 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this record 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only 156144 2a000023x1 2006-08-02 19:29:53.000 This works but I cannot include any additional fields: select hole_name,max(date_time)as Mtime FROM Rcompl where program_id in (select program_id from nc_programs where job_id ='156') group by hole_name Result: hole_name Mtime 2a000020x1 2006-08-02 19:18:34.000 2a000021x1 2006-08-02 19:19:45.000 2a000022x1 2006-08-02 19:29:18.000 2a000023x1 2006-08-02 19:29:53.000 So how do I get this to work with all the 28 fields. Be glad to provide more info. Thanks for the help. Skip wrote:
Show quote > I am reposting this from an earlier question. I have tried to be more Have a look here:> clear about what I need. > > I need to get the max row date from the following query. There is a > date field in rcompl.date_time. There can be several rows with matching > hole_name field but with different dates. I need only the latest of > these rows. > > Below is a sample of data, problem is that the 'duplicate data' is for > the > hole_name field only. But I need to include all the data. I have > simplified the query for the example. > > Sample Data: > id hole_name Mtime > 156140 2a000020x1 2006-08-02 19:18:34.000 > 156141 2a000021x1 2006-08-02 19:19:45.000 > 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this > record > 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only > 156144 2a000023x1 2006-08-02 19:29:53.000 > > > This works but I cannot include any additional fields: > > > select hole_name,max(date_time)as Mtime > FROM Rcompl > where program_id in (select program_id from nc_programs where job_id > ='156') > group by hole_name > > > Result: > hole_name Mtime > 2a000020x1 2006-08-02 19:18:34.000 > 2a000021x1 2006-08-02 19:19:45.000 > 2a000022x1 2006-08-02 19:29:18.000 > 2a000023x1 2006-08-02 19:29:53.000 > > > So how do I get this to work with all the 28 fields. > > > Be glad to provide more info. Thanks for the help. > http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html Thanks I was able to get the following to work,
select Exceptions.* FROM Rcompl as Exceptions INNER JOIN(SELECT hole_name,max(date_time) AS LastDate FROM rcompl GROUP BY hole_name) AS LastDates ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time = LastDates.LastDate where program_id in (select program_id from nc_programs where job_id ='156') order by Exceptions.hole_name Now I have to incorporate it into this any suggestions?? select rcompl.*,fastener_database.dbo.fastener_db_working.* FROM Rcompl LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = fastener_db_working.[Serial Number] where len(hole_name)>2 and status<>'5' and program_id in (select program_id from nc_programs where job_id =@job_id) order by hole_name Thanks Tracy McKibben wrote: Show quote > Skip wrote: > > I am reposting this from an earlier question. I have tried to be more > > clear about what I need. > > > > I need to get the max row date from the following query. There is a > > date field in rcompl.date_time. There can be several rows with matching > > hole_name field but with different dates. I need only the latest of > > these rows. > > > > Below is a sample of data, problem is that the 'duplicate data' is for > > the > > hole_name field only. But I need to include all the data. I have > > simplified the query for the example. > > > > Sample Data: > > id hole_name Mtime > > 156140 2a000020x1 2006-08-02 19:18:34.000 > > 156141 2a000021x1 2006-08-02 19:19:45.000 > > 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this > > record > > 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only > > 156144 2a000023x1 2006-08-02 19:29:53.000 > > > > > > This works but I cannot include any additional fields: > > > > > > select hole_name,max(date_time)as Mtime > > FROM Rcompl > > where program_id in (select program_id from nc_programs where job_id > > ='156') > > group by hole_name > > > > > > Result: > > hole_name Mtime > > 2a000020x1 2006-08-02 19:18:34.000 > > 2a000021x1 2006-08-02 19:19:45.000 > > 2a000022x1 2006-08-02 19:29:18.000 > > 2a000023x1 2006-08-02 19:29:53.000 > > > > > > So how do I get this to work with all the 28 fields. > > > > > > Be glad to provide more info. Thanks for the help. > > > > Have a look here: > http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com This query is used as a dataset for a SQL 2005 Report. Can this query
be split into several pieces so that it is easier to read and less likely to make mistakes? I do not know where to start to put this togethor into 1 query. Skip wrote: Show quote > Thanks I was able to get the following to work, > > select Exceptions.* > FROM Rcompl as Exceptions > > INNER JOIN(SELECT hole_name,max(date_time) AS LastDate > FROM rcompl GROUP BY hole_name) AS LastDates > ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time > = LastDates.LastDate > > where program_id in (select program_id from nc_programs where job_id > ='156') > order by Exceptions.hole_name > > > Now I have to incorporate it into this any suggestions?? > > select rcompl.*,fastener_database.dbo.fastener_db_working.* > FROM Rcompl > LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON > substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = > fastener_db_working.[Serial Number] > where len(hole_name)>2 and status<>'5' and program_id in (select > program_id from nc_programs where job_id =@job_id) > order by hole_name > > Thanks > > > > > Tracy McKibben wrote: > > Skip wrote: > > > I am reposting this from an earlier question. I have tried to be more > > > clear about what I need. > > > > > > I need to get the max row date from the following query. There is a > > > date field in rcompl.date_time. There can be several rows with matching > > > hole_name field but with different dates. I need only the latest of > > > these rows. > > > > > > Below is a sample of data, problem is that the 'duplicate data' is for > > > the > > > hole_name field only. But I need to include all the data. I have > > > simplified the query for the example. > > > > > > Sample Data: > > > id hole_name Mtime > > > 156140 2a000020x1 2006-08-02 19:18:34.000 > > > 156141 2a000021x1 2006-08-02 19:19:45.000 > > > 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this > > > record > > > 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only > > > 156144 2a000023x1 2006-08-02 19:29:53.000 > > > > > > > > > This works but I cannot include any additional fields: > > > > > > > > > select hole_name,max(date_time)as Mtime > > > FROM Rcompl > > > where program_id in (select program_id from nc_programs where job_id > > > ='156') > > > group by hole_name > > > > > > > > > Result: > > > hole_name Mtime > > > 2a000020x1 2006-08-02 19:18:34.000 > > > 2a000021x1 2006-08-02 19:19:45.000 > > > 2a000022x1 2006-08-02 19:29:18.000 > > > 2a000023x1 2006-08-02 19:29:53.000 > > > > > > > > > So how do I get this to work with all the 28 fields. > > > > > > > > > Be glad to provide more info. Thanks for the help. > > > > > > > Have a look here: > > http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html > > > > > > -- > > Tracy McKibben > > MCDBA > > http://www.realsqlguy.com This is what I came up with it works but is there a better way of doing
this? I create a new table first of all the data in the orginal query, then narrow it down in the select. drop table TempExceptions --go select rcompl.*,fastener_database.dbo.fastener_db_working.* into TempExceptions FROM Rcompl LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = fastener_db_working.[Serial Number] where len(hole_name)>2 and program_id in (select program_id from nc_programs where job_id =@job_id) order by hole_name --go select Exceptions.* FROM TempExceptions as Exceptions INNER JOIN(SELECT hole_name,max(date_time) AS LastDate FROM rcompl GROUP BY hole_name) AS LastDates ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time = LastDates.LastDate where status<>5 and program_id in (select program_id from nc_programs where job_id =@job_id) order by Exceptions.hole_name Skip wrote: Show quote > This query is used as a dataset for a SQL 2005 Report. Can this query > be split into several pieces so that it is easier to read and less > likely to make mistakes? I do not know where to start to put this > togethor into 1 query. > > > Skip wrote: > > Thanks I was able to get the following to work, > > > > select Exceptions.* > > FROM Rcompl as Exceptions > > > > INNER JOIN(SELECT hole_name,max(date_time) AS LastDate > > FROM rcompl GROUP BY hole_name) AS LastDates > > ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time > > = LastDates.LastDate > > > > where program_id in (select program_id from nc_programs where job_id > > ='156') > > order by Exceptions.hole_name > > > > > > Now I have to incorporate it into this any suggestions?? > > > > select rcompl.*,fastener_database.dbo.fastener_db_working.* > > FROM Rcompl > > LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON > > substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = > > fastener_db_working.[Serial Number] > > where len(hole_name)>2 and status<>'5' and program_id in (select > > program_id from nc_programs where job_id =@job_id) > > order by hole_name > > > > Thanks > > > > > > > > > > Tracy McKibben wrote: > > > Skip wrote: > > > > I am reposting this from an earlier question. I have tried to be more > > > > clear about what I need. > > > > > > > > I need to get the max row date from the following query. There is a > > > > date field in rcompl.date_time. There can be several rows with matching > > > > hole_name field but with different dates. I need only the latest of > > > > these rows. > > > > > > > > Below is a sample of data, problem is that the 'duplicate data' is for > > > > the > > > > hole_name field only. But I need to include all the data. I have > > > > simplified the query for the example. > > > > > > > > Sample Data: > > > > id hole_name Mtime > > > > 156140 2a000020x1 2006-08-02 19:18:34.000 > > > > 156141 2a000021x1 2006-08-02 19:19:45.000 > > > > 156142 2a000022x1 2006-08-02 19:28:54.000 'Do not to return this > > > > record > > > > 156143 2a000022x1 2006-08-02 19:29:18.000 'Return this one only > > > > 156144 2a000023x1 2006-08-02 19:29:53.000 > > > > > > > > > > > > This works but I cannot include any additional fields: > > > > > > > > > > > > select hole_name,max(date_time)as Mtime > > > > FROM Rcompl > > > > where program_id in (select program_id from nc_programs where job_id > > > > ='156') > > > > group by hole_name > > > > > > > > > > > > Result: > > > > hole_name Mtime > > > > 2a000020x1 2006-08-02 19:18:34.000 > > > > 2a000021x1 2006-08-02 19:19:45.000 > > > > 2a000022x1 2006-08-02 19:29:18.000 > > > > 2a000023x1 2006-08-02 19:29:53.000 > > > > > > > > > > > > So how do I get this to work with all the 28 fields. > > > > > > > > > > > > Be glad to provide more info. Thanks for the help. > > > > > > > > > > Have a look here: > > > http://realsqlguy.com/serendipity/archives/10-Gimme-The-Latest-And-Greatest!.html > > > > > > > > > -- > > > Tracy McKibben > > > MCDBA > > > http://www.realsqlguy.com Skip wrote:
Show quote > Thanks I was able to get the following to work, You should be able to just include the INNER JOIN to LastDates as part > > select Exceptions.* > FROM Rcompl as Exceptions > > INNER JOIN(SELECT hole_name,max(date_time) AS LastDate > FROM rcompl GROUP BY hole_name) AS LastDates > ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time > = LastDates.LastDate > > where program_id in (select program_id from nc_programs where job_id > ='156') > order by Exceptions.hole_name > > > Now I have to incorporate it into this any suggestions?? > > select rcompl.*,fastener_database.dbo.fastener_db_working.* > FROM Rcompl > LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON > substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = > fastener_db_working.[Serial Number] > where len(hole_name)>2 and status<>'5' and program_id in (select > program_id from nc_programs where job_id =@job_id) > order by hole_name > > Thanks > of the original query, only with RCompl instead of Exceptions. So, between these two lines: FROM Rcompl LEFT OUTER JOIN fastener.... Make that: FROM Rcompl INNER JOIN ..... AS LastDates ON Rcompl.hole_name = LastDates.hole_name AND Rcompl.date_time = LastDates.LastDate LEFT OUTER JOIN fastener.... I tried this but was having trouble with it complaining about ambigious
names. I went back to the other method but I changed to create a ##temp table. It makes more sense to me this way. Do you see any problems with the following? Thanks for the help!! drop table ##TempExceptions select rcompl.*,fastener_database.dbo.fastener_db_working.* into ##TempExceptions FROM Rcompl LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = fastener_db_working.[Serial Number] where len(hole_name)>2 and program_id in (select program_id from nc_programs where job_id =@job_id) select Exceptions.* FROM ##TempExceptions as Exceptions where date_time = (select max(date_time) from ##TempExceptions as Mdate where Exceptions.hole_name = Mdate.hole_name) and status<>5 order by Exceptions.hole_name Tracy McKibben wrote: Show quote > Skip wrote: > > Thanks I was able to get the following to work, > > > > select Exceptions.* > > FROM Rcompl as Exceptions > > > > INNER JOIN(SELECT hole_name,max(date_time) AS LastDate > > FROM rcompl GROUP BY hole_name) AS LastDates > > ON Exceptions.hole_name = LastDates.hole_name AND Exceptions.date_time > > = LastDates.LastDate > > > > where program_id in (select program_id from nc_programs where job_id > > ='156') > > order by Exceptions.hole_name > > > > > > Now I have to incorporate it into this any suggestions?? > > > > select rcompl.*,fastener_database.dbo.fastener_db_working.* > > FROM Rcompl > > LEFT OUTER JOIN fastener_database.dbo.fastener_db_working ON > > substring(Rcompl.hole_name,1,len(Rcompl.hole_name)-2) = > > fastener_db_working.[Serial Number] > > where len(hole_name)>2 and status<>'5' and program_id in (select > > program_id from nc_programs where job_id =@job_id) > > order by hole_name > > > > Thanks > > > > You should be able to just include the INNER JOIN to LastDates as part > of the original query, only with RCompl instead of Exceptions. So, > between these two lines: > > FROM Rcompl > LEFT OUTER JOIN fastener.... > > Make that: > > FROM Rcompl > INNER JOIN ..... AS LastDates > ON Rcompl.hole_name = LastDates.hole_name AND Rcompl.date_time = > LastDates.LastDate > LEFT OUTER JOIN fastener.... > > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com Skip wrote:
> I tried this but was having trouble with it complaining about ambigious You would be better off making the single query work. You can avoid the > names. I went back to the other method but I changed to create a ##temp > table. It makes more sense to me this way. Do you see any problems with > the following? Thanks for the help!! > ambiguous column names by always specifying a prefix on your column references. For instance, in the ORDER BY clause of the original query you posted, you have: ORDER BY hole_name When you include the LastDates derived table, you now have two tables that contain a column named "hole_name". Change the ORDER BY to read: ORDER BY RCompl.hole_name Look for other places where this occurs. Also, you should avoid using "*" in your field list, it makes it easier to make these kinds of mistakes, and is considered bad practice.  |
|||||||||||||||||||||||
Other interesting topics