Sybase SQL基于具有ID的多列选择不同 [英] Sybase SQL Select Distinct Based on Multiple Columns with an ID

问题描述

我正在尝试查询一个sybase服务器，以获取我们为测试目的而持有的不同类型数据的示例。

I'm trying to query a sybase server to get examples of different types of data we hold for testing purposes.

我有一个如下表（抽象）

I have a table that looks like the below (abstracted)

Animals table:
id | type | breed           | name
------------------------------------
1  | dog  | german shepard  | Bernie
2  | dog  | german shepard  | James
3  | dog  | husky           | Laura
4  | cat  | british blue    | Mr Fluffles
5  | cat  | other           | Laserchild
6  | cat  | british blue    | Sleepy head
7  | fish | goldfish        | Goldie

我想要每种类型的示例，因此上表需要一个结果集就像（实际上，我只想要ID）：

As I mentioned I want an example of each type so for the above table would like a results set like (in reality I just want the ID's):

id | type | breed           
---------------------------
1  | dog  | german shepard  
3  | dog  | husky          
4  | cat  | british blue   
5  | cat  | other          
7  | fish | goldfish    

我尝试了以下多种查询组合，但它们都是无效的SQL（对于sybase）或返回无效结果

I've tried multiple combinations of queries such as the below but they are either invalid SQL (for sybase) or return invalid results

  SELECT id, DISTINCT ON type, breed FROM animals
  SELECT id, DISTINCT(type, breed) FROM animals
  SELECT id FROM animals GROUP BY type, breed

在另一列上发现了其他问题，例如 SELECT DISTINCT ，但这仅处理一列

I've found other questions such as SELECT DISTINCT on one column but this only deal with one column

您是否知道如何实现此查询？

Do you have any idea how to implement this query?

推荐答案

也许您必须使用汇总函数 max 或 min 作为列ID。

Maybe you have to use aggregate function max or min for column ID. It will return only one ID for grouped columns.

select max(Id), type, breed 
from animals
group by type, breed 

编辑：

其他实现方法：

具有汇总功能

select id, type, breed  
from animals 
group by type, breed  
having id = max(Id)

具有子查询和汇总子查询

select id, type, breed 
from animals a1
group by type, breed 
having id = (
               select max(id)
               from animals a2
               where a2.type = a1.type
               and   a2.breed = a1.breed
            )

这篇关于Sybase SQL基于具有ID的多列选择不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！