SQL order by problem ?

[simonwraight]

I have been trying to figure a way of solving the following problem all day yesterday. I know I can do it with vb script but there must be a way to do it in the query.
The following query returns a list of demands with attached remarks. I would like to return each dmdID only once with the most recent remark using the rmkDate column.
Here is my query which returns all remarks for all demands.

SELECT TOP 100 PERCENT tblDemand.DmdNum,
tblDemandRemarks.DmdID,
tblRemarks.RemarkDesc,
tblRemarks.RmkDate,
tblRoles.Role
FROM tblDemand INNER JOIN
tblDemandRemarks ON tblDemand.DmdID = tblDemandRemarks.DmdID INNER JOIN
tblRemarks ON tblDemandRemarks.RmkID = tblRemarks.RmkID INNER JOIN
tblRoles ON tblRemarks.RoleID = tblRoles.roleID
ORDER BY tblDemandRemarks.DmdID, tblRemarks.RmkDate DESC

[mic2100]

You shud just need to add a GROUP BY in your query...


SELECT tblDemand.DmdNum,
tblDemandRemarks.DmdID,
tblRemarks.RemarkDesc,
tblRemarks.RmkDate,
tblRoles.Role
FROM tblDemand INNER JOIN
tblDemandRemarks ON tblDemand.DmdID = tblDemandRemarks.DmdID INNER JOIN
tblRemarks ON tblDemandRemarks.RmkID = tblRemarks.RmkID INNER JOIN
tblRoles ON tblRemarks.RoleID = tblRoles.roleID
GROUP BY tblDemandRemarks.DmdID
ORDER BY tblDemandRemarks.DmdID, tblRemarks.RmkDate DESC

[guelphdad]

mic2100, the GROUP BY clause is incorrect, you have to name each column in the select clause in the GROUP BY clause.


simon what you want is latest record per group (http://guelphdad.wefixtech.co.uk/sqlhelp/latestfromgroup.shtml)

[simonwraight]

Thanks guelphdad that appears to be the solution I will give it a go today. I did however solve the problem by using a cursor and creating a temporary table which worked fine but I felt it was a bit long winded for what I wanted. I tried mics solution and it did not work but thanks for trying.


By the way I tried to click on the thanks for help button and was told I don't have the right permissions ??

[abduraooft]

By the way I tried to click on the thanks for help button and was told I don't have the right permissions ?? Seems like you've no javascript support in your browser(?)

[simonwraight]

Javascript is disabled on this internet machine at uni.

I wonder if someone could show me the syntax for this solution. I have tried the solution suggested and with all the tables being joined I cannot seem to get it to work. The example given was for 1 table but i have to have 4 joined together.

Sorry to be a pain I really have tried this for ages and as said before have a different solution but would be very interested in seeing how this one would work.

[guelphdad]

Simon
try the join with the two necessary tables and then add the others after you have that part working.