0

Possible Duplicate:
Oracle SQL - How to Retrieve highest 5 values of a column

I'm writing oracle query but stuck in the following problem

table is like this:

**Tool**       **Name**       **Gender**
Facebook   Alice        F
Facebook   Alex         M
Facebook   Loong        M
Facebook   Jimmy        M
Twitter    James        M
Twitter    Jessica      F
Twitter    Sam          M
Twitter    Kathrine     F
Google     Rosa         F
Google     Lily         F
Google     Bob          M

What I wanna get is the first female in each tool the result should be like:

Facebook   Alice
Twitter    Jessica
Google     Rosa

I'm trying to get this by using query not functions or procedures Thank for helping

Community
  • 1
  • 1
Ivan Li
  • 1,850
  • 4
  • 19
  • 23
  • How do you know Kathrine isn't the first female for Twitter? – Mike Sherrill 'Cat Recall' Oct 28 '12 at 18:17
  • Your definition of "first" is ambiguous. 'Lily' clearly comes before 'Rosa' in an alphabetical sort, so to make 'Rosa' come ahead of 'Lily' requires a different ordering criterion. You haven't provided any additional columns, so it is impossible to achieve your proposed result set in a deterministic fashion. Please edit your question to clarify your requirement: either include the additional sort column(s) or state that you just want a random row as the "first" one. – APC Oct 28 '12 at 21:00

2 Answers2

4
select  *
from    (
        select  row_number() over (partition by tool order by name) as rn
        ,       Name
        ,       Tool
        from    YourTable
        where   Gender = 'F'
        ) SubQueryAlias
where   rn = 1 -- Only first per tool

Example at SQL Fiddle.

Andomar
  • 232,371
  • 49
  • 380
  • 404
1

This is another alternative.

select min(name), tool
from yourTable
where gender = 'F'
group by tool

I'd like to have a little bit of a discussion on which is better or which does what, for me its the first time I see row_number(). Note thas this one returns the female in the alphabetical order, yours does the same by sorting in a window, what is the difference?

Roger
  • 2,912
  • 2
  • 31
  • 39
  • +1 good answer-- this works as long as you don't have to select other columns besides Name. For example, if you have to return first and last name, `min` might pick the first name of one person, and the last name of another. – Andomar Oct 28 '12 at 20:19