mcmuney Posted March 20, 2007 Share Posted March 20, 2007 I'm using this code to pull latest unique results, but the when I use the GROUP, it's not showing all the members. When I remove the GROUP, it shows all, but I need to use it so it doesn't repeat. Any ideas? $sql = "select s1.* from scm_invite_history s1,sc_member_images s2 where s1.scm_mem_id=s2.scm_mem_id and s2.sci_main=1 GROUP BY s1.scm_mem_id ORDER BY s1.sih_id DESC LIMIT 0,$l"; $new_mem = $db->select_data($sql); Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/ Share on other sites More sharing options...
marmite Posted March 20, 2007 Share Posted March 20, 2007 SELECT DISTINCT column from table ? Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211508 Share on other sites More sharing options...
mcmuney Posted March 20, 2007 Author Share Posted March 20, 2007 That's strange. If I remove GROUP and add DISTINCT, the results are same. It's skipping some results, for example, it's skipping the first two and starts from the 3rd and it also skips other from the middle. I don't understand why. Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211521 Share on other sites More sharing options...
per1os Posted March 20, 2007 Share Posted March 20, 2007 You need to read up on GROUP BY's. With a group by you really need to define every column in order. The *'s really screw it up. Try listing out the columns and see what happens. Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211529 Share on other sites More sharing options...
marmite Posted March 20, 2007 Share Posted March 20, 2007 Hard to tell without seeing the result set but I would guess the actual results are different somehow. My advice is to build up from the most basic query (leave out the ORDER By and DESC and just select s1.* and see what happens). If that does the same thing, your data must be different. Having said that (and I am no SQL expert), it seems odd you have select s1.* and then name a table s1. is that a column and a table with the same name?? surely that wouldn't work Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211548 Share on other sites More sharing options...
per1os Posted March 20, 2007 Share Posted March 20, 2007 Having said that (and I am no SQL expert), it seems odd you have select s1.* and then name a table s1. is that a column and a table with the same name?? surely that wouldn't work No, s1.* selects all columns from table s1. s1 is being defined here: from scm_invite_history s1,sc_member_images s2 in the from section. He could also have it as: select scm_invite_history.* instead of s1, in essence s1 is an alias. Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211560 Share on other sites More sharing options...
mcmuney Posted March 20, 2007 Author Share Posted March 20, 2007 Here's a modified line (if I remove DISTINCT, I get all lines, but I need to show unique lines only and using either DISTINCT or GROUP BY results in the same problem): $sql = "select DISTINCT s1.scm_mem_id from scm_invite_history s1,sc_member_images s2 where s1.scm_mem_id=s2.scm_mem_id and s2.sci_main=1 ORDER BY s1.sih_id DESC LIMIT 0,8"; Actual Data in Database (table scm_invite_history): sih_id scm_mem_id 401 2383 400 2383 399 2383 398 2383 395 732 394 2318 393 1176 392 1176 391 2460 390 2460 389 2460 388 2460 387 2460 386 2460 385 2460 384 2332 383 539 382 1 381 1 380 1 379 1 378 2432 377 2432 376 2432 375 2417 374 2441 373 2444 371 2417 370 2105 369 2105 365 1402 RESULTS BEING DISPLAYED: 2383 1176 2460 2432 2441 2444 2417 1402 Link to comment https://forums.phpfreaks.com/topic/43555-help-with-groub-by/#findComment-211632 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.