CODEWITHSUNDEEP
sqlsql-server
Efe
Efe

Reputation: 289

group by clause help

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

Upvotes: 2

Views: 156

Answers (5)

Kieveli
Kieveli

Reputation: 11075

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.

Upvotes: 2

Tamil.SQL
Tamil.SQL

Reputation: 262

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!

Upvotes: 0

Paul Sasik
Paul Sasik

Reputation: 81429

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.

Upvotes: 0

KM.
KM.

Reputation: 103589

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

Upvotes: 0

Aaron Fi
Aaron Fi

Reputation: 10396

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?

Upvotes: 0

Related Questions