mysql question

seby

perhaps this is confusing ..

I have rows where column "news_id" have other rows with the same news_id number.

how do I select just one row IF there are other rows with the same news_id but also allow more than 1 result that aren't the same news_id? and selecting the rows based on column "time" (latest).

See attached image, I want to fetch the second row since it's the newest, but want to get other rows that are different, in other words, I want to use "LIMIT 10" but making sure those results don't have the same news_id.

Hope that makes sense.

cgraz

Try

SELECT DISTINCT news_id, id, poster, bbuserid, time FROM table GROUP BY news_id ORDER BY time LIMIT 10

Cgraz

seby

thanks cgraz, it works ... but its not sorting properly, if sorting by time it puts the id's like this:

58
59
60

I guess that would be accending, (newest at the bottom), but i want newest at the top (highest id),. so i tried:

GROUP BY news_id ORDER by news_id DESC LIMIT 10

tried this too, still sorts oldest to newest:

GROUP BY news_id DESC

any ideas?

seby

Hmm i think i know why...

SELECT DISTINCT id, news_id FROM table GROUP BY news_id DESC

but, i need to sort it by id DESC not news_id DESC, ... is it possible? since i am using DISTINCT here?

cause i dont think you can have GROUP BY and ORDER BY on the in the same query, right?

cgraz

yeah, i used GROUP BY so only news_id would be selected distinct and not the other fields. But since you're selecting just news_id and id, it doesn't really matter cause the id field will be unique anyways.

Since you're just selecting those two, then do

SELECT DISTINCT id, news_id FROM table ORDER BY id DESC

Cgraz

seby

that returns more than 1 row with the same news_id 🙁

cgraz

SELECT DISTINCT news_id, id FROM table GROUP BY news_id ORDER BY id DESC

What about that?

Cgraz

seby

same as before, it lists the proper results, but it's sorting the ID's in ASC.

I think when you have GROUP BY, then ORDER BY doesn't do anything ..

seby

shouldn't this work:

SELECT DISTINCT news_id, id FROM table GROUP BY id DESC

group by ID instead of news_id?

hmmm doesn't work either, that lists more that one result with the same news_id, how is that possible with we're using DISTINCT ?

cgraz

I just tried the following on my server (except using my tables field names) and it worked just fine.

SELECT DISTINCT news_id, id FROM table GROUP BY news_id ORDER BY id DESC;

Cgraz

seby

Now that works, but its sorting the ID in ASC LoL 😃

i dunno why, are you sure you can have GROUP BY column and also have ORDER BY different column in the same query? i dont think it's possible since GROUP is over riding.

seby

i will just do it as ASC i guess, not really a big deal 😉

thanks for the help.