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.
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
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
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
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
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
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

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
We are always happy to see You again here!