在Excel ODBC SQL中选择具有不同第一列的行

Given the table below, how can I select only rows that have a unique animal?

根据下表,我如何只选择具有独特动物的行?

animal   age    location
-------------------------
zebra    4      africa        -->  return this row
owl      50     america       -->  return this row
owl      50     u.s.a
panda    9      china         -->  return this row

My understanding is that the following won't work due to location having unique rows:

我的理解是,由于具有唯一行的位置,以下内容将无法工作:

SELECT DISTINCT animal, age, location FROM table

2 个解决方案

#1

Short answer - it is impossible, because condition is not strict, how you can determine which row for "owl" should be returned?

简短回答 - 这是不可能的,因为条件不严格,你如何确定应该返回“owl”的哪一行?

Long answer, as soon as you add some conditions, you can select something close to your question, like this:

答案很长,只要添加一些条件,就可以选择接近问题的内容,如下所示:

SELECT animal, MIN(age), MIN(location) FROM table
GROUP BY animal

in this query we're selecting unique animals and minimal values for age and location, note - it doesn't mean that there will be row with such values, like here:

在这个查询中,我们选择了独特的动物和年龄和位置的最小值,请注意 - 这并不意味着会有这样的值的行,如下所示:

animal1, 2, location1
animal1, 1, location2

this query will select:

此查询将选择:

animal1, 1, location1

#2

Short answer - it is possible with a subquery to a partition table. I find it best to create a View in your MS SQL that defines the partition query and then call that view in your SELECT statement.

简短回答 - 可以使用子查询到分区表。我发现最好在MS SQL中创建一个定义分区查询的View,然后在SELECT语句中调用该视图。

The MS SQL View (e.g. vw_First_Animals) would be:

MS SQL视图(例如vw_First_Animals)将是:

SELECT * FROM (
  SELECT *, rn=row_number() OVER (PARTITION BY [animal] ORDER by [location]) FROM [dbo].[tblAnimals] 
) x where rn = 1;

This makes your XL query,

这使你的XL查询,

SELECT * FROM vw_First_Animals

If you reshape the ORDER BY clause to the View, you can change the order that the unique returns are delivered.

如果将ORDER BY子句重新整形为View,则可以更改传递唯一返回的顺序。

I would be remiss without nodding toward this thread that has helped me with PARTITION table subqueries through its simplistic demonstration.

如果不通过简单的演示帮助我使用PARTITION表子查询,那么我就会失职。

#1