Page 1 of 1
SQL Query v7
Posted: 29 Jun 2009, 12:45
by GrimStoner
Consider this :
I have two tables; 1 contains a list of objects with uniqueidentifiers as primary keys. In table 2, each row contains the following data :
ObjectId (uniqueidentifier)
ToTime (datetime)
with the ObjectId being a FK for the uniqueidentifier on table 1.
I want to return a list of Objects in table 1, that has at least one row in table 2, but also, if it has more than one row in table 2, it should return the row with the latest ToTime.
Re: SQL Query v7
Posted: 29 Jun 2009, 13:32
by SykomantiS
The following will give you at least everything that has a corresponding ID in both tables, though I'm unsure how to filter if there are more than two with the same foreign key. I'll dig around and see what I can find.
Code: Select all
SELECT TOP 1 *
FROM dbo.table1
INNER JOIN dbo.table2 on dbo.table1.objectId = dbo.table2.objectId
ORDER BY ToTime DESC
Edit: you might need to add a where clause at the end with some sort of 'top 1' as a filter.
Edit2: Try the edited code, should be pretty close, though I can't say with 100% certainty.
Re: SQL Query v7
Posted: 29 Jun 2009, 13:57
by GrimStoner
That code only gives the first row of the whole table... Not the first row in each group of ObjectId's.
Re: SQL Query v7
Posted: 29 Jun 2009, 13:58
by SykomantiS
what happens if you remove the 'top 1' from the statement?
Re: SQL Query v7
Posted: 29 Jun 2009, 14:04
by GrimStoner
Well, then it'll return all the rows in the joined table, with the ToTime column sorted.
Re: SQL Query v7
Posted: 29 Jun 2009, 14:13
by SykomantiS
Well, what about using a cursor? Though I can't help you with that, sorry
Re: SQL Query v7
Posted: 29 Jun 2009, 14:14
by GrimStoner
Cursors = slow.
Re: SQL Query v7
Posted: 29 Jun 2009, 14:15
by SykomantiS
But would it work?
Re: SQL Query v7
Posted: 29 Jun 2009, 15:18
by rustypup
Code: Select all
SELECT T1.ObjectId, MAX(T2.ToTime)
FROM Table2 T2 WITH(NOLOCK) LEFT JOIN Table1 T1 WITH(NOLOCK) ON T1.ObjectId=T2.ObjectId
GROUP BY T1.ObjectID
ORDER BY T1.ObjectID
?
Re: SQL Query v7
Posted: 29 Jun 2009, 15:22
by RuadRauFlessa
lol
Code: Select all
SELECT
[dbo].[table1].*,
(SELECT TOP 1 [dbo].[table2].[ToTime] FROM [dbo].[table2] WITH (NOLOCK) where [dbo].[table1].[objectId] = [dbo].[table2].[objectId]) AS [Table2ToTime]
FROM [dbo].[table1] WITH (NOLOCK)
just add more of the inner selects for the other cols you need.
Re: SQL Query v7
Posted: 29 Jun 2009, 15:23
by RuadRauFlessa
rustypup wrote:Code: Select all
SELECT T1.ObjectId, MAX(T2.ToTime)
FROM Table2 T2 WITH(NOLOCK) LEFT JOIN Table1 T1 WITH(NOLOCK) ON T1.ObjectId=T2.ObjectId
GROUP BY T1.ObjectID
ORDER BY T1.ObjectID
?
It is not guaranteed that he only needs the one value. Maybe he wants the other values from table2 for the last record associated with the record in table1.
Re: SQL Query v7
Posted: 29 Jun 2009, 15:24
by RuadRauFlessa
GrimStoner wrote:Cursors = slow.
that and for about 80% of all thins SQL you don't need them.