Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Nov 2007
    Posts
    40
    Thanks
    9
    Thanked 0 Times in 0 Posts

    SQL order by problem ?

    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.

    Code:
    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

  • #2
    Regular Coder mic2100's Avatar
    Join Date
    Feb 2006
    Location
    Scunthorpe
    Posts
    562
    Thanks
    15
    Thanked 28 Times in 27 Posts
    You shud just need to add a GROUP BY in your query...

    Code:
    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

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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

  • #4
    New Coder
    Join Date
    Nov 2007
    Posts
    40
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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 ??

  • #5
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,853
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    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(?)
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #6
    New Coder
    Join Date
    Nov 2007
    Posts
    40
    Thanks
    9
    Thanked 0 Times in 0 Posts
    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.

  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Simon
    try the join with the two necessary tables and then add the others after you have that part working.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •