distinct, recent ??

GimboMedlem sedan dec. 20001 626 inlägg

Trådstart11 okt. 2004 17:27#1

hejsan har en sql som ser ut pa foljande:

SELECT DISTINCT 
                      ccStatus.orderID AS orderID, ccStatus.OrderStatus AS orderStatus, orders.fName AS fName, orders.lName AS lName, 
                      orders.Time_stamp AS time_stamp
FROM         orders INNER JOIN
                      ccStatus ON orders.ID = ccStatus.orderID
ORDER BY ccStatus.time_stamp DESC

i denna relation kan det dyka up dubletter pa orderID i ccStatus tabellen vilket medfor att nar jag listar ut innehallet sa visas dubletterna, jag vill endast lista ut, den senaste orderID'et och endast en av de inte alla... distinct funkar inte i min relation just nu?? ngn som vet hur?

LarsGMedlem sedan dec. 200012 464 inlägg

12 okt. 2004 07:09#2

SELECT 
ccStatus.orderID, 
ccStatus.OrderStatus, 
orders.fName, 
orders.lName, 
max(orders.Time_stamp) AS time_stamp
FROM         orders INNER JOIN
ccStatus ON orders.ID = ccStatus.orderID
group by ccStatus.orderID, 
ccStatus.OrderStatus, 
orders.fName, 
orders.lName
ORDER BY time_stamp DESC