A counter that limits the rows returned by a select statement

I have a select statement that is rather complicated. However, I have a column, let's say date, and a column called title, then I want a counter (it increments if date is not equal previous date) that count each day in the resultset, when counter reach 10 I want the SQL statement to return the resultset for the the 10 first dates.

Dates are just an analogy of my problem; the important part is my counter.

EDIT:

Here is my real problem:

I want to get the 10 first conversation along with their latest message and time. I want the sql to retrieve the invited in the conversation too(I do this by adding rows with same conversationId latestMessage and time along with distinct values of profileId. I group the invited to each conversation in JAVA code later). I have one table called conversation_invited and another table called conversation messages. In table conversation_messages I have columns like conversationId, profileId, messageBody, timeStamp etc. And in table conversation_invited only column conversationId and profileId.

SELECT M1.conversationId,  M1.profileId, profiles.profileMiniature, profiles.firstName, profiles.lastName, M2.lastMessageTime, count(DISTINCT M2.lastMessageTime) AS nb, M3.messageBody AS lastMessage " +
            "FROM conversation_invited AS M1 " +
            "INNER JOIN ( " +
            "           SELECT  conversation开发者_C百科Id, " +
            "                   max(timeStamp) AS lastMessageTime" +
            "           FROM conversation_messages " +
            "           WHERE conversationId in     ( " +
            "                                       SELECT conversationId" +
            "                                       FROM conversation_invited " +
            "                                       WHERE profileId = ? " +
            "                                       ) " +
            "           GROUP BY conversationId " +
            "           ) AS M2 " +
            "ON M1.conversationId = M2.conversationId " +
            "LEFT JOIN conversation_messages AS M3 " +
            "ON M2.lastMessageTime = M3.timeStamp " +
            "INNER JOIN profiles " +
            "ON profiles.profileId = M1.profileId " +
            "WHERE  M1.profileId <> ? " +
                    // WHICH COLUMN SHALL I GROUP WITH?
            "HAVING nb < 10 " +
            "ORDER BY M2.lastMessageTime DESC ";

UPDATE: I have removed the group statement in my query. I have one problem above and that is none of the values in the columns are distinct to each other. As I said:

want to get the 10 first conversation along with their latest message and time. I want the sql to retrieve the invited in the conversation too(I do this by adding rows with same conversationId latestMessage and time along with distinct values of profileId. I group the invited to each conversation in JAVA code later)

But profileId is not distinct either(I mean distinct in each conversation, but a user can be invited tio many conversations). The question is how Can I add a column that always have distinct values in an easy way? So that I can group the counter?

---

Look at the GROUP BY and DISTINCT expressions (in conjunction with LIMIT). You should be able to use these to find the dates you like; you may then need to use those as a sub-query to retrieve the rows you like (maybe someone else will figure out how to do it all in one go, or maybe you will!).

---

Hard to give a very good answer without some more solid info, but could you select out your top 10 unique dates and then inner join those dates into the main query to return the results you want?

I'm thinking something like:

with top_10_unique_dates as (
select top 10 distinct date from
table_whatever
where blah = blah
order by something
)

select * from
table_whatever
where blah = blah
inner join top_10_unique_dates
on table_whatever.date = top_10_unique_dates.date

---

in Oracle, you get LEAD and LAG window functions that allow this type of row to row value analysis.

---

You want all the titles for the first 10 dates?

select title, date
from yourtable
where date in (
    select distinct date
    from yourtable
    order by date asc
    limit 10
)

---

SELECT title, COUNT(DISTINCT date) AS nb
FROM myTable
GROUP BY title
HAVING nb > 10
LIMIT 10