What is the sql query for this?

Question

Id  Project_Id       Activity_Time        Username
1      100        2008-01-01 11:12:13         A
2      100        2008-01-01 00:00:00         B
3      500        2008-02-01 00:00:00         C
4      300        2008-02-03 00:00:00         D
5      500        2008-03-03 11:11:11         A
6      300        2008-04-04 00:00:00         D
7      500        2008-05-05 00:00:00         C
8      200        2008-06-06 00:00:00         D
9      100        2009-01-01 11:12:13         A
10     300        2010-01-01 01:02:03         A

What is the sql query to select Project_Id based on following input :

for the given username
order by Activity_Time - latest first
distinct Project_Id
only 10 rows

I tried few queries nothing worked, so seeking help here. Currently H2 database is used but it would change over a period.

[Update] This is a true project requirement, not a home work, am a newbie, please don't make fun, am learning the stuffs.

A sample of what you expect to see would be better than your description of what you want. — Welbog, Feb 10 '10 at 15:29
Show us what you tried, and why it didn't work, you will learn more from your own mistakes than you will by us just giving you a solution. — Binary Worrier, Feb 10 '10 at 15:32
The flavour of DBMS does matter when it come the syntax for limiting by rowcount. — APC, Feb 10 '10 at 15:36
Actually the information is there. He mentioned "H2". Didn't realize this until rereading. — Frank Kalis, Feb 10 '10 at 15:36
aren't the tags "sql" and "h2" mutually exclusive? I thought h2 was a way to access a database using only java and with no sql. **Hey OP, do you want a SQL select statement or the h2 java code?** — KM., Feb 10 '10 at 15:46
Guys, this is not a homework, this true project requirement. Am looking for SQL statement. — Maniganda Prakash, Feb 10 '10 at 15:59
@Frank Kalis - yep I missed that too. Mind you, OP says "it would change over a period." — APC, Feb 10 '10 at 16:00

Gabriele Petrioli · Accepted Answer · 2010-02-10T17:01:21.227

8

This should be a good start if not completely working ...

SELECT TOP 10 
    project_ID, max(activity_time) as activity_time
FROM
    table_name
WHERE
    username = usernameVariable
GROUP BY 
    project_id
ORDER BY 
    activity_time DESC

edited Feb 10 '10 at 17:01

answered Feb 10 '10 at 15:51

Gabriele Petrioli

191,379
34
261
317

1

Following exception thrown when the above query is executed : Order by expression ACTIVITY_TIME must be in the result list in this case; SQL statement: – Maniganda Prakash Feb 10 '10 at 16:10
1

The DISTINCT is now not needed. – JBrooks Feb 10 '10 at 16:53

What is the sql query for this?

1 Answers1

Linked