如何只获取oracle中特定列的第一个实例

I am writing a query, and I sorted my data on first a unique identifier for a person, then the date created...

我正在编写一个查询,我首先将数据排序为一个人的唯一标识符,然后创建日期...

So for each new instance a unique id I only want that row.. the rest can be thrown away. I tried a nested select statement:

因此,对于每个新实例,我只需要该行的唯一ID ...其余的可以丢弃。我尝试了一个嵌套的select语句:

 SELECT d.full_name as "Name",
        d.id as "Unique Identifier",
        s.service_name as "service",
        t.create_date as "date created"
        from 
        data d,
        transaction t,
        system s
        where
         ...
        s.system = 251 
        order by d.id, t.create_date desc

and I get an error each time as I assume I am not using group by correctly. What is the best way to get only the first instance of a new unique identifier and throw the rest away?

我每次都会收到一个错误,因为我认为我没有正确使用组。获取新唯一标识符的第一个实例并抛弃其余标识符的最佳方法是什么?

Edit:

Here is some sample data:

以下是一些示例数据:

Name         Unique Identifier      service          date created

john doe     1                      eca              1/14/2008 
john doe     1                      ecb              1/10/2008 
john doe     1                      eca              11/12/2007
henry ford   2                      ford1            06/07/2010
henry ford   2                      ford2            08/09/2009
jack johnson 4                      burgers1         11/01/2013
jack johnson 4                      burgers2         09/06/2007

so all I want is these lines to print:

所以我想要的是打印这些线条:

john doe     1                      eca              1/14/2008 
henry ford   2                      ford1            06/07/2010
jack johnson 4                      burgers1         11/01/2013

which are the most recent requests per each person.. but each line still may have different data.

这是每个人最近的请求..但每行仍然可能有不同的数据。

1 个解决方案

#1

~~If I'm reading your question correct I believe you want the first row for every unique id?~~

如果我正在读你的问题,我相信你想要每个唯一身份证的第一行?

EDIT : when you added the sample data it became much easier to understand what you want.

编辑:当你添加样本数据时,它变得更容易理解你想要的。

As you haven't added the database structure my query does only involves one table, not three as your sample query, because I can't tell how they are joined. It should be easy to adapt the query to the proper tables and joins though.

由于您没有添加数据库结构,我的查询只涉及一个表,而不是三个作为您的示例查询,因为我无法分辨它们是如何连接的。应该很容易使查询适应正确的表和连接。

The following query should do it:

以下查询应该这样做:

SELECT 
  t1.full_name AS "Name",
  t1.id AS "Unique Identifier",
  t1.service_name AS "Service",
  t1.create_date AS "Date created"
FROM table1 t1,
  (SELECT id, MAX(create_date) AS last_date
   FROM table1
   GROUP BY id) t2
   WHERE t1.id = t2.id
  AND t1.create_date = t2.last_date
ORDER BY t1.id

See this sample SQL Fiddle (Oracle 11g R2) for an example.

有关示例,请参阅此示例SQL Fiddle(Oracle 11g R2)。

Output from the query above:

上述查询的输出:

|         NAME | UNIQUE IDENTIFIER |  SERVICE |                    DATE CREATED |
|--------------|-------------------|----------|---------------------------------|
|     john doe |                 1 |      eca |  January, 14 2008 01:00:00+0000 |
|   henry ford |                 2 |    ford1 |     June, 07 2010 02:00:00+0000 |
| jack johnson |                 4 | burgers1 | November, 01 2013 01:00:00+0000 |

#1