How to group rows and select a "sample" match in one go

I have a table in a Derby database of the form of

Status   Name
0        Name1
0        Name2
1        Name3
1        Name4
2        NameX
...

I use the following to summarize how often each status happens

SELECT COUNT(*), STATUS
FROM MYTABLE
GROUP BY STATUS

Now I would like to add one sample-name for each status (does not matter which one), i.e. I would like a result similar to

Count    Status     Sample
2        0          Name2
2        1          Name3
1        2          NameX
...

I tried using sub-query together with ROW_NUMBER(), but couldn't get it to work.

So is there a way to write a SQL statement which includes one name from the data as shown?

Answers


Are you trying to make the names "random", if not, you could try

select count(*), max(Name), Status
from MYTABLE
group by Status

Need Your Help

NHibernate with MVC 2 Deployment issue

nhibernate iis asp.net-mvc-2

I am deploying Asp.Net MVC 2 application on Windows Server 2003.

How to recover data from journal file?

sqlite

I have a database and its journal. I want to recover data from a deleted entry. The journal should contain both addition of the data to the DB and removal from it. How can I do this?