0

Was wondering if people could give me a hand here. I am currently updating some SQL code for a plugin I maintain, and I have hit a problem.

What I am trying to do is fetch data from one table, join it with another and then return the data to my PHP app. That is working, however - I want to order the data returned in descending order - this is proving difficult and doesn't seem to want to play ball.

SELECT DISTINCT s.userid, s.id, u.user_nicename, u.user_login, u.display_name, u.id
       FROM wp_bbpas as s INNER JOIN wp_users as u
       ON s.userid = u.id
       ORDER BY s.id DESC

That returns everything in wp_bbpas and joins it with the wp_users table, but I actually only want to grab unique/distinct values.

Am I doing something wrong here?

Jake Ball
  • 798
  • 2
  • 8
  • 26
  • Please provide sample data and desired results. It is not clear what you really intend from the query -- the query should be working. – Gordon Linoff Apr 17 '16 at 13:18
  • 1
    I think s.id should be left out, since it appers to be the unique key of wp_bbpas – Turo Apr 17 '16 at 13:18
  • @Turo that solved it, please submit an answer so I can mark it as resolved. – Jake Ball Apr 17 '16 at 13:23
  • @Turo, so what if I wanted to select additional columns in this query? e.g, I also want to grab the date from the bbpas table. Obviously, this is going to be unique in 99% of cases - so how can I avoid dupes appearing in my results but also getting this data? – Jake Ball Apr 17 '16 at 13:34
  • with DSTINCT you will not get dulicate rows,. you get one row for every set of different values of the columns from wp_bbpas.Sinc s.id is unique you get all rows. If you want a single row for each user you have to make a pivot table with the columns of wp_bbpas, see http://stackoverflow.com/questions/12598120/mysql-pivot-table-query-with-dynamic-columns – Turo Apr 17 '16 at 13:47
  • @Turo I am not quite sure that is what I am looking for, also - I am unable to use those since I am working with WordPress here. Are you saying I am unable to get unique rows, inner joined, whilst also pulling through additional columns? I have tried Group by, however - the columns come through in a random order and thus not useful. – Jake Ball Apr 18 '16 at 21:20
  • If you only need aggregates of the wp_bbpas-columns, you should group by wp_bbpas.userid and join the result table with wp_users. Then is the DISTINCT obsolete. – Turo Apr 19 '16 at 05:13

1 Answers1

1

Since s.id appears to be the unique key of wp_bbpas, it should be left out of the SELECT list to reduce the result to distinct users.

Turo
  • 4,724
  • 2
  • 14
  • 27