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?
Are you trying to make the names "random", if not, you could try
select count(*), max(Name), Status from MYTABLE group by Status