Min values

Freshman

Registered User.
Local time
Tomorrow, 01:47
Joined
May 21, 2010
Messages
437
Hi all,

I have 2 tables with a one to many relationship displaying orders for each Client.

If I run a query on all orders by Client, I get multiple lines for each Client which is to be expected but I need only one line per Client displaying only the most recent order by date. Not displaying any older orders.
I played around with the sum and 'orderby' min date but not getting a single line per Client.
Can anyone assist please?

Thanks
 
Show the SQL-String.
 
This would give multiple lines:

Code:
SELECT Clients.Name1, Clients.Surname, Clients.CompNum, Orders.Code, Orders.DateCompleted
FROM Clients INNER JOIN Orders ON Clients.ID = Orders.ID;
 
SELECT Clients.Name1, Clients.Surname, Clients.CompNum, max(Orders.Code) as orderCode, Max(Orders.DateCompleted) As DateCompleted
FROM Clients INNER JOIN Orders ON Clients.ID = Orders.ID
Group By Clients.Name1, Clients.Surname, Clients.CompNum
 
@arnelgp - think I said thanks to quickly - I noticed that the orders don't line up with the dates.
So at first it seems to work but if you look at the unfiltered lines, then the data displayed are not correct.
Any idea?
 
SELECT Client.id Clients.Name1, Clients.Surname, Clients.CompNum, max(Orders.Code) as orderCode, Max(Orders.DateCompleted) As DateCompleted
FROM Clients INNER JOIN Orders ON Clients.ID = Orders.ID
Group By client.id Clients.Name1, Clients.Surname, Clients.CompNum

ok, are the joins really on Clients.ID and Orders.ID?
 
Last edited:
SELECT T1.id T1.Name1, T1.Surname, T1.CompNum, max(T2.OrderCode) as OrderCode, (SELECT TOP 1 DateCompleted FROM Orders T3 WHERE T3.ID = T1.ID AND T3.OrderCode = (SELECT MAX(OderCode) FROM Orders T4 WHERE T4.ID=T1.ID)) AS DateCompleted
FROM Clients T1 INNER JOIN Orders T2 ON T1.ID = T2.ID
Group By T1.id T1.Name1, T1.Surname, T1.CompNum
 
Thanks for the SQL but it confuses me a bit. Seems like I have to create extra tables?
I tried but still not working.
Could you maybe post the sample db you did it on here?
 
Thanks a lot. Can only check it out tomorrow night but sure it will be what i need.
Regards :)
 
@arnelgp - thanks once again for that - it is an impressive piece of SQL string and still a bit baffling. The ordercode and orderdate was swapped around (the result giving the max order instead of the max date) but that gave me an opportunity to edit the string which was great to get it right.
Thanks :)
 

Users who are viewing this thread

Back
Top Bottom