SQL Query v7
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
SQL Query v7
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.
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.
-
- Registered User
- Posts: 14085
- Joined: 06 Oct 2004, 02:00
- Location: Location, Location...
- Contact:
Re: SQL Query v7
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.
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.
Code: Select all
SELECT TOP 1 *
FROM dbo.table1
INNER JOIN dbo.table2 on dbo.table1.objectId = dbo.table2.objectId
ORDER BY ToTime DESC
Edit2: Try the edited code, should be pretty close, though I can't say with 100% certainty.
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v7
That code only gives the first row of the whole table... Not the first row in each group of ObjectId's.
-
- Registered User
- Posts: 14085
- Joined: 06 Oct 2004, 02:00
- Location: Location, Location...
- Contact:
Re: SQL Query v7
what happens if you remove the 'top 1' from the statement?
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
Re: SQL Query v7
Well, then it'll return all the rows in the joined table, with the ToTime column sorted.
-
- Registered User
- Posts: 14085
- Joined: 06 Oct 2004, 02:00
- Location: Location, Location...
- Contact:
Re: SQL Query v7
Well, what about using a cursor? Though I can't help you with that, sorry
-
- Registered User
- Posts: 716
- Joined: 08 Oct 2004, 02:00
- Contact:
-
- Registered User
- Posts: 14085
- Joined: 06 Oct 2004, 02:00
- Location: Location, Location...
- Contact:
Re: SQL Query v7
But would it work?
- rustypup
- Registered User
- Posts: 8872
- Joined: 13 Dec 2004, 02:00
- Location: nullus pixius demonica
- Contact:
Re: SQL Query v7
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
Most people would sooner die than think; in fact, they do so - Bertrand Russel
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v7
lol
just add more of the inner selects for the other cols you need.
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)
- Spoiler (show)
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v7
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.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
- Spoiler (show)
-
- Registered User
- Posts: 20576
- Joined: 19 Sep 2003, 02:00
- Location: Bloodbank
Re: SQL Query v7
that and for about 80% of all thins SQL you don't need them.GrimStoner wrote:Cursors = slow.
- Spoiler (show)