Minimum values

[woony]

hi,

I'm making a query that has quite some joins and there is no other way in linking the tables to get to my preferred value.

Because of this I get a resultset that has x-amount of values.
From this recordset I only need the one(s) where the date is lowest. So I thought using min(date) I'd get there. Or even using top(1). The problem now is, it is possible that the resultset contains more than one record with the same date. When this date is the same I do need both or more of these records because that means they have another action.

Anyone has a clue how I can solve this without splitting up my query. Like I was planning to do. get my joins to get that lowest date. And then link that date to the other tables to get the resultset that returns all those lines.

This might sound complicated :p and it kind of is.

This is my query that returns multiple lines thx in advance

select o.Order_id, v.Voertuig, o.DatumVoertuigIn ,op.Planningscode_id ,p.Omschrijving, v.Mileage
from [Order] o
inner join Order_Planningscode op on convert(varchar(50),op.Order_id) = convert(varchar(50),o.Order_id)
inner join Vosp v on v.PlanningscodeID = op.Planningscode_id and v.VoertuigID = o.Voertuig_id
inner join Planningscode p on p.Planningscode_id = v.PlanningscodeID
inner join Planning pl on o.Order_id = pl.Order_id
where convert(varchar(10), o.DatumVoertuigIn, 20) + ' ' + '00:00' < '2008-03-07 00:00'
and v.Datum > '2008-05-09'
--group by o.Order_id, v.Voertuig, o.DatumVoertuigIn, op.Planningscode_id,p.Omschrijving, v.Mileage

[deepuv04]

hi,

I'm making a query that has quite some joins and there is no other way in linking the tables to get to my preferred value.

Because of this I get a resultset that has x-amount of values.
From this recordset I only need the one(s) where the date is lowest. So I thought using min(date) I'd get there. Or even using top(1). The problem now is, it is possible that the resultset contains more than one record with the same date. When this date is the same I do need both or more of these records because that means they have another action.

Anyone has a clue how I can solve this without splitting up my query. Like I was planning to do. get my joins to get that lowest date. And then link that date to the other tables to get the resultset that returns all those lines.

This might sound complicated :p and it kind of is.

This is my query that returns multiple lines thx in advance

select o.Order_id, v.Voertuig, o.DatumVoertuigIn ,op.Planningscode_id ,p.Omschrijving, v.Mileage
from [Order] o
inner join Order_Planningscode op on convert(varchar(50),op.Order_id) = convert(varchar(50),o.Order_id)
inner join Vosp v on v.PlanningscodeID = op.Planningscode_id and v.VoertuigID = o.Voertuig_id
inner join Planningscode p on p.Planningscode_id = v.PlanningscodeID
inner join Planning pl on o.Order_id = pl.Order_id
where convert(varchar(10), o.DatumVoertuigIn, 20) + ' ' + '00:00' < '2008-03-07 00:00'
and v.Datum > '2008-05-09'
--group by o.Order_id, v.Voertuig, o.DatumVoertuigIn, op.Planningscode_id,p.Omschrijving, v.Mileage

Hi,
In the above query you are using two dates in the where clause, which date must be the least date.( [o.DatumVoertuigIn] or v.Datum ).

Thanks