Clever Geek Handbook

Group by clause help

I am using facebook type request page for a project. I am stuck with grouping requests on all requested print pages.

my sql like

SQL = "SELECT R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"
SQL = SQL & " FROM REQUESTS R, MEMBERS M"
SQL = SQL & " WHERE R.ACTIVE = 1 AND R.TOID = "& Session("MEMBERID") &" AND R.BYID = M.MEMBERID"
SQL = SQL & " GROUP BY R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"
SQL = SQL & " ORDER BY R.DATEENTERED DESC"

Returns results as
Friend
Edie
Friend
Frank
Group
George

But I need it like


Edie
Frank 's Friend George
Group

+2


source to share


5 answers


if there are multiple REQUESTS for one of the LOCATIONS you are after, you will receive duplicates.

Look at this:



SELECT --select all columns required, can produce duplicates
    R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME
    FROM REQUESTS            R
        INNER JOIN MEMBERS   M ON R.BYID = M.MEMBERID
    WHERE M.MEMBERID IN (SELECT --get distinct list of members to display
                             M.MEMBERID
                             FROM REQUESTS            R
                                 INNER JOIN MEMBERS   M ON R.BYID = M.MEMBERID
                             WHERE  R.ACTIVE = 1 AND R.TOID = ___MEMBERID__
                             GROUP BY M.MEMBERID
                        )
    ORDER BY R.DATEENTERED DESC
0


source


Try changing it to a separate selection. I think this is vendor specific, but usually

SQL = "SELECT DISTINCT R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"

or



SQL = "SELECT DISTINCT ROW R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"

Will do the trick.

+2


source


Grouping will give you unique values ​​for the column that your group is grouping in.

Why do you want the Friend string to appear twice?

0


source


There are several fields in your SELECT and GROUP BY clauses, which from their names sound like primary key values ​​like MEMBERID or fields that can be very unique like DATEENTERED (this latter probably includes the info time.) Any of these fields may prevent your query from performing any grouping at all. I would try to remove some of these items until your grouping takes effect. Thus, you isolate which of the fields is problematic. Or keep a few fields like first and last name and then add them to see where GROUP BY falls apart.

0


source


I just added DISTINCT at the top of the query. I guess this will solve it if I understood correctly.

SQL = "SELECT DISTINCT R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"

SQL = SQL and "FROM REQUESTS R, MEMBERS M"

SQL = SQL and "WHERE R.ACTIVE = 1 AND R.TOID =" & Session ("MEMBERID") & "AND R.BYID = M.MEMBERID"

SQL = SQL and "GROUP BY R.REQUESTID, R.BYID, R.TOID, R.TYPE, R.DATEENTERED, M.MEMBERID, M.FIRSTNAME, M.LASTNAME"

SQL = SQL and "ORDER BY R.DATEENTERED DESC"

Try it if the work votes!

0


source






More articles:


All Articles