SQL Query v7

Get help on databases - MySQL, Oracle, Access, etc.
Post Reply
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

SQL Query v7

Post 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.
Image
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Re: SQL Query v7

Post 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.
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL Query v7

Post by GrimStoner »

That code only gives the first row of the whole table... Not the first row in each group of ObjectId's.
Image
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Re: SQL Query v7

Post by SykomantiS »

what happens if you remove the 'top 1' from the statement?
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL Query v7

Post by GrimStoner »

Well, then it'll return all the rows in the joined table, with the ToTime column sorted.
Image
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Re: SQL Query v7

Post by SykomantiS »

Well, what about using a cursor? Though I can't help you with that, sorry :oops:
GrimStoner
Registered User
Posts: 716
Joined: 08 Oct 2004, 02:00
Contact:

Re: SQL Query v7

Post by GrimStoner »

Cursors = slow.
Image
SykomantiS
Registered User
Posts: 14085
Joined: 06 Oct 2004, 02:00
Location: Location, Location...
Contact:

Re: SQL Query v7

Post by SykomantiS »

But would it work?
User avatar
rustypup
Registered User
Posts: 8872
Joined: 13 Dec 2004, 02:00
Location: nullus pixius demonica
Contact:

Re: SQL Query v7

Post 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 
?
Most people would sooner die than think; in fact, they do so - Bertrand Russel
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v7

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v7

Post 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.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
RuadRauFlessa
Registered User
Posts: 20576
Joined: 19 Sep 2003, 02:00
Location: Bloodbank

Re: SQL Query v7

Post by RuadRauFlessa »

GrimStoner wrote:Cursors = slow.
that and for about 80% of all thins SQL you don't need them.
:rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock: :rock:
Spoiler (show)
Intel Core i7-2600k @ 3.4GHz
Corsair Vengence 2x4GB DDR3 2000MHz
Thermaltake Toughpower 850W
ASUS nVidia GTX560 1GB
CoolerMaster HAF 932
Post Reply