locked
ORDER BY with a Group by? RRS feed

  • Question

  • I think this is where I ask this type of question..

    I'm trying to figure out the best way to write this query with a group by:

    SELECT TOP 100 d.DebtorID, l.Recall
    FROM debts d inner join debtors l on d.DebtorID = l.DebtorID 
    WHERE d.ClientCode = 225 AND d.Balance > 0.00 AND l.Recall LIKE '76%' 
    ORDER BY l.Recall ASC

    I need a group by on the DebtorID as I could get anything from 1 to 40+ results with the same DebtorID (however I only care that there is at least one).  I'm not really sure a better way to explain it other than that.
    Tuesday, April 2, 2013 7:45 PM

Answers

  • Decided to handle it on the code side since it doesn't appear that SQL can do it, which is no problem.  I could use temp tables and what not, but for me it's just easier to use which ever language to do the additional processing of the query.  So far grabbing a recordset of 1000 rows has been more than enough as I think the most amount of rows I went through was 250ish, so I may drop it down to 500 - 700 just for the sake of resources.
    • Marked as answer by temlehdrol Wednesday, April 10, 2013 12:06 PM
    Wednesday, April 10, 2013 12:06 PM

All replies

  • Hi temlehdrol,

    What is the data type of debtors Recall column, since we need to aggregate this column if we specify “group by d.DebtorID”, otherwise, put Recall column in select column will generate an error. If Recall column data type is int, we can sum them of each group, please refer to the following commands:

    SELECT TOP 100 d.DebtorID, sum(l.Recall) as Recalls
    FROM debts d inner join debtors l on d.DebtorID = l.DebtorID 
    WHERE d.ClientCode = 225 AND d.Balance > 0.00 AND l.Recall LIKE '76%' 
    group by I.DebtorID
    ORDER BY sum(l.Recall) ASC
    

    If Recall column data type is varchar, we can use “FOR XML PATH" to connect the varchar data within the same group.

    How to use GROUP BY to concatenate strings in SQL Server?
    http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server



    Allen Li
    TechNet Community Support

    Wednesday, April 3, 2013 9:18 AM
  • Recall is a varchar(12)... recall contains two identifier "codes" the first two characters, then the 3rd and 4th followed by a date or it could be null.  The problem is I really only care about the first two for this query, thus the Recall LIKE '76%' part.  Problem two is I could run into a case where the same DebtorID could have different placementDate,;which I just realized this query is missing, but that's fine (I'm going to end up with multiple similar queries for this project).

    I'm guessing I'm going to have to handle this on the "code side" of the application?  Grab a result set larger than I need, lets say 1000 to have some room for duplicates.  Loop through the results (row by row) comparing the DebtorId in the recordset to the array or datatable or something to track what I already have and skipping that row if I already have a record of that DebtorId untill I hit 100 or what ever number I decide.  (makes sense right :-) )

    Wednesday, April 3, 2013 12:21 PM
  • Recall is a varchar(12)... recall contains two identifier "codes" the first two characters, then the 3rd and 4th followed by a date or it could be null.  The problem is I really only care about the first two for this query, thus the Recall LIKE '76%' part.  Problem two is I could run into a case where the same DebtorID could have different placementDate,;which I just realized this query is missing, but that's fine (I'm going to end up with multiple similar queries for this project).

    I'm guessing I'm going to have to handle this on the "code side" of the application?  Grab a result set larger than I need, lets say 1000 to have some room for duplicates.  Loop through the results (row by row) comparing the DebtorId in the recordset to the array or datatable or something to track what I already have and skipping that row if I already have a record of that DebtorId untill I hit 100 or what ever number I decide.  (makes sense right :-) )

    how's going on
    Tuesday, April 9, 2013 8:39 AM
  • Decided to handle it on the code side since it doesn't appear that SQL can do it, which is no problem.  I could use temp tables and what not, but for me it's just easier to use which ever language to do the additional processing of the query.  So far grabbing a recordset of 1000 rows has been more than enough as I think the most amount of rows I went through was 250ish, so I may drop it down to 500 - 700 just for the sake of resources.
    • Marked as answer by temlehdrol Wednesday, April 10, 2013 12:06 PM
    Wednesday, April 10, 2013 12:06 PM