By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,373 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Minimum values

P: 11
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
Mar 20 '08 #1
Share this Question
Share on Google+
1 Reply


deepuv04
Expert 100+
P: 227
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
Mar 20 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.