Page 1 of 1

[56K WARN] selecting distinct column with others

Posted: Sun Aug 26, 2007 1:57 pm
by blade_922
Hey,

i have this code

Code: Select all

$latestfile = mysql_query("SELECT DISTINCT cat_id FROM `ccms_screenshots`  GROUP BY id ORDER BY `id`  DESC limit 4");




  $mydata = array(); //create the data holder 
 while($row = mysql_fetch_assoc($latestfile)){....

Okay this will allow me to show distinct cat_id's in descending order. But how do i display other columns from the database?


If i try this

Code: Select all

$latestfile = mysql_query("SELECT DISTINCT cat_id,title,url,date FROM `ccms_screenshots`  GROUP BY id ORDER BY `id`  DESC limit 4");


Then the distinct query will stop working and doesnt show cat_id distinct.

The first one i posted at the top of this post is working fine, but will not allow me to show other info from the rows. how would i extract the other data like url and title etc?

Posted: Sun Aug 26, 2007 2:11 pm
by xpgeek
Using distinct for grouping is not good practice.

You can add cat_id column to group section instead of useing distinct.

Code: Select all

$latestfile = mysql_query("SELECT cat_id,title,url,date FROM `ccms_screenshots`  GROUP BY cat_id, id ORDER BY `id`  DESC limit 4");
And don't forget to start reading some books on mysql - i recommended Mysql cookbook or Mysql Tutorial by from MySQL AB

Posted: Sun Aug 26, 2007 2:22 pm
by blade_922
That doesnt work, because it then shows more than one of the cat_id

i want to show the latest images and there info but only one of each cat_id, so if there are 2 images of cat_id 33 then it will just show one, so its gota be sorted by id then i want it to filter out the query to show only one row of the cat_id


the query you posted makes it show more than one row of a cat_id

Posted: Sun Aug 26, 2007 2:31 pm
by xpgeek
First try this:

Code: Select all

$latestfile = mysql_query("SELECT cat_id,title,url,date FROM `ccms_screenshots`
  GROUP BY cat_id desc, id ORDER BY `id`  DESC limit 4");
Wich version of mysql if more then 4 then try subquery.

Code: Select all

$latestfile = mysql_query("SELECT cat_id,title,url,date FROM `ccms_screenshots`
where cat_id in (select cat_id from `ccms_screenshots` group by cat_id desc)
  GROUP BY id ORDER BY `id`  DESC limit 4");
If it not help, show you table structre and sample of data.

Posted: Sun Aug 26, 2007 2:53 pm
by blade_922
Okay neither worked.

First up when i use this code

Code: Select all

SELECT DISTINCT cat_id FROM `ccms_screenshots`  GROUP BY id ORDER BY `id`  DESC limit 4
It manages to display one of each cat_id and sorts by id even though its not showing id. Its exactly what i want, but there are other colums to these rows like date, url and title. i need these other columns to show the title and date etc.

Image


now with this code

Code: Select all

SELECT cat_id,title,url,date FROM `ccms_screenshots` 
  GROUP BY cat_id desc, id ORDER BY `id`  DESC limit 10
(I changed limit to 10 to show u structure)

Okay and this query above produces
Image


Okay here you can see that it begins to show all of cat_id column, instead of just one of each of the cat_id.

I only want to display one row(preferably latest) of cat_id 32 and one of cat_id 230 and one row of cat_id 229


You see the first code at the top shows this but just doesnt display the rest of the columns.

Posted: Sun Aug 26, 2007 2:59 pm
by xpgeek
Shoot

Code: Select all

SELECT cat_id, title, url, `date` FROM `ccms_screenshots`  GROUP BY cat_id DESC, `date` DESC limit 4
i hope it helps.

Posted: Sun Aug 26, 2007 3:09 pm
by blade_922
hey lol,

didnt work :(

That query gave this

Image


It should be sorting by id column so it shows latest because loads are added everyday

ive checked and the first 4 cat_id should be this

Image

But its not showing that.

Posted: Sun Aug 26, 2007 3:47 pm
by blade_922
alright

I now have this

Code: Select all

SELECT DISTINCT title,date,cat_id,title,timestamp from ccms_screenshots ORDER BY id desc limit 10
Image

When i use this it works perfectly and shows this image above. BUT whenever i put the url into the query so it reads the query it stops working and shows this below

Image

so for some reason putting 'url' into the query it makes it not work right even putting 'id' as well because in the image above its showing multiple of the same cat_id which i dont want instead of like the first image.


aaargghh is there any other way of being able to read the url in a seperate query?

Regards

Posted: Sun Aug 26, 2007 6:43 pm
by blade_922
ive worked it out.

cheers for your help everyone :D

Posted: Mon Aug 27, 2007 7:05 am
by xpgeek
If you need sort by id, then add to my last query Order by id :wink:

We are always happy to see You again here!