Problems with using DISTINCT

[Shadowing]

Hey guys im having a issue with getting DISTINCT to work

 

im pulling 2 columns using pagination

 

so im counting first and then selecting but it still giving me duplicate owners

 

SELECT COUNT(DISTINCT owner) FROM planets WHERE id != ''

SELECT DISTINCT owner, homes FROM planets WHERE id != '' ORDER BY homes DESC LIMIT $offset, $rowsperpage

[Christian F.]

Are you certain that the "owner" values are indeed identical? You could also try with GROUP BY, but should give pretty much the same result as DISTINCT.

Use var_dump () to check the data you're retrieving, and post some example data showcasing the problem here.

[Shadowing]

Thanks for the reply ChristianF

 

Here is my return

 

 

[Jessica]

That is the correct result - the owner LordDougy for example is distinct for homes 30, 23, 20.

[Shadowing]

thanks for the reponse jesirose

 

so thats how that works

 

hmm well what im trying to do is make it so the owner names are not duplicating

[Shadowing]

I think i got the correct return now using group by

 

SELECT COUNT(*) FROM planets WHERE id != '' group by owner

SELECT owner,$building FROM planets WHERE id != '' group by owner ORDER BY $building DESC LIMIT $offset, $rowsperpage

[Shadowing]

i think its working now but order by is being ignored. I guess maybe i cant say group by order by

[Jessica]

What DO you want? Why are you selecting the other variable when there is more than one match?

[Shadowing]

Its a list of planets

 

players own more then one planet

 

each planet has a level of homes on them

 

im trying to grab the players with the highest level of homes in order with out having duplicates.

 

 

so like lets say one player is holding 1st and 2nd place of the highest level of homes I dont want it to grab the 2nd place only the 1st.

 

lol hope that makes sense.

[Christian F.]

So, you want to select the total sum of homes, grouped by the individual users?

 

(That was a big hint, btw.)

[Shadowing]

nah no total

 

 

In the picture i posted

 

Notice LordDougy is holding 1st ,5th and 10th place

 

i dont want his 5th and 10th place to be included

 

 

so Matei should be in 5th instead.

 

so once it grabs a user it no longer grabs that user again

[Christian F.]

OK, so how would you modify the above statement to accurately describe what you want? Do it, and you should pretty much have your SQL query finished.

[Shadowing]

So it should end up

 

LordDougy 30

Rumo 28

Lordcruz 25

Neimenljivi 25

Matei 23

Apofis 22

Ashrak 20

Yahweh 20

[Jessica]

I think it would be

Select distinct user, homes from tbl group by user order by homes

[Christian F.]

Hmm... I should perhaps have clarified my previous post, and made it clear it was my previous statement (question, really) that I was referring to. Not the desired end results.

 

Jesi: I don't think you need DISTINCT in there.

[Shadowing]

 

that gives me this return

 

I think its correct but its doing it backwards grabing every users lowest amount

 

 

ChristainF: ya i understood you wanted me to write statement but i have no idea i couldnt even write it in english so you guys could understand what im trying to do hehe

[Jessica]

Try

Select user, homes from tbl group by user order by homes DESC

 

[Shadowing]

Alright i got this to work

 

Select owner, homes, MAX(homes) AS homes from planets where owner != '' GROUP BY owner order by homes DESC

[PFMaBiSmAd]

If all you want is the maximum value for each owner, you would use MAX(homes) as mh, then GROUP BY owner. You would also ORDER BY mh DESC to get the result in the order that you want them.

 

However, if you want the specific row holding the maximum for each owner (perhaps you want the planet name or the row id), you would need to do this - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

[Shadowing]

Thanks PFMaBiSmAd

 

few minutes to late

[PFMaBiSmAd]

Edit to your edited query: Your originally posted query was getting the homes value from the first row encountered in the table for each owner.