group by clause help

Question

I am coding facebook kind request page for a project. I am stuck with grouping requests in all requests printed page.

my sql as

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 me results as
Friend
Edie
Friend
Frank
Group
George

But I need it as

Friend
Edie
Frank
Group
George

Answer 1

Grouping by is going to give you unique values for the column by which your grouping.

Why do you want the Friend row listed twice?

Answer 2

if there is more than one REQUESTS for one of the MEMBERS you are after, you will get 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

Answer 3

There are a number of fields in your SELECT and GROUP BY clauses which, from the their names, sound like either primary key values such as MEMBERID or fields likely to be very unique, such as DATEENTERED (this last one probably includes time info as well.) Any one of these fields could prevent your query from doing any kind of grouping at all. i would try removing some of these items until your grouping starts to take effect. This way you'll isolate which of the fields are problematic. Or, Keep a few fields such as first and last name and then add on to see where the GROUP BY falls apart.

Answer 4

I just added a DISTINCT on the top of the query. I guessing that would solve if I got it right.

SQL = "SELECT DISTINCT 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"

Try this if works vote it!

Answer 5

Try changing it to a distinct select. I think this is provider 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.