Returning unique values in one column

[neil6179]

Hi,

I currently have a table called “pages” containing the following columns:
pageid, domainid, location, lastread

I've put together a query that returns 10 rows with the lowest “lastread” values, for which this works fine:
SELECT * FROM pages ORDER BY lastread ACS LIMIT 0,10;

I now the have the added complication that I want to make sure each value in “domainid” is unique. That is to say, not repeated within the 10 results returned. I though I might be able to do something using “GROUP BY domainid” but that cannot just work on the one column as far as I can see.

Also the “DISTINCT” keyword only ensures the entire row is unique.

If anyone can offer me some advice on how I could go about this or if its ever possible from one SQL statement I would be most grateful.

Thanks
Neil

 

[cLFlaVA]

try:

  select domainid
       , min(lastread) mindate
    from pages
group by domainid
order by mindate asc
   limit 0, 10

*cLFlaVA
----------------------------
[tt]I already made like infinity of those at scout camp...[/tt]
beware of active imagination: [URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[neil6179]

Hi,

Thanks for that it works perfectly!
Unfortunately it takes about 22 minutes for the query to run! :-(
I currently have 2.8 million rows in the table and that could easily double.

Can anyone give me some pointers on how I could reduce this time or an I asking a bit much of mySQL here?

Thanks again for you input.

Neil

 

[r937]

first of all, are there any indexes on this table? is there a primary key?

and what will you be doing with these 10 dates?



r937.com | rudy.ca