0

The below query is a simple select, but i need to select Nationality as distinct, while also selecting type and category (both not distinct). Any way to do so?

SELECT NATIONALITY,TYPE,CATEGORIE FROM EMPLOYEE
John Odom
  • 1,189
  • 2
  • 20
  • 35
mikeb
  • 709
  • 2
  • 9
  • 35

2 Answers2

1

Try:

SELECT NATIONALITY,TYPE,CATEGORIE FROM EMPLOYEE 
Group by NATIONALITY,TYPE,CATEGORIE

I'm assuming that you have same type and category for same nationality. In the other possibility where you can have different categories and types for the same nationality:

You have different records for each nationality, and you want to select one for each, but what should be the value of TYPE and CATEGORY? you need to chose one using an aggregate function (Max, Min, Avg...)

Ex:

SELECT NATIONALITY,max(TYPE),max(CATEGORIE) FROM EMPLOYEE 
Group by NATIONALITY
Haytem BrB
  • 1,528
  • 3
  • 16
  • 23
  • no each nationality has different types and categories – mikeb May 03 '16 at 16:24
  • According to [this answer](http://stackoverflow.com/a/54430/2843157), this query would be like writing `SELECT DISTINCT NATIONALITY,TYPE,CATEGORIE FROM EMPLOYEE`. – John Odom May 03 '16 at 16:24
  • 1
    @JohnOdom: the answer says: `SELECT DISTINCT a,b,c FROM t` is roughly equivalent to: `SELECT a,b,c FROM t GROUP BY a,b,c` – Haytem BrB May 03 '16 at 16:27
  • @mikeb I added a second part in the answer, where you can have different catergories. – Haytem BrB May 03 '16 at 16:28
  • 1
    @haytem I know, and mikeb was asking for only distinct on nationality, but your edit is a much better example. – John Odom May 03 '16 at 16:29
0

Assuming the structure of EMPLOYEE table is similar to below-

NATIONALITY  TYPE  CATEGORY
American     A     NR
American     C     SR
American     C     SR
American     B     GEN
Indian       A     SR
Indian       A     NR
Indian       B     NR
Indian       B     NR
Indian       B     GEN
Italian      C     GEN
Italian      A     SR
Italian      B     SR

And the requirement is to fetch one record for each NATIONALITY with any combination of TYPE and CATEGORY available for that NATIONALITY.

To do so, you can first partition the table on NATIONALITY and add row_number (). The Partition function maps the rows of a table into partitions based on the values of a specified column. Using row_number() on that, will add row numbers from 1 to N for each NATIONALITY. Then later this can be used with a where clause for row_number() = 1.

Query 1

SELECT *, row_number() over(partition by NATIONALITY order by Nationality) 
as  RowNum from EMPLOYEE

The result will be as follows:

NATIONALITY  TYPE  CATEGORY  RowNum
American     A     NR        1
American     C     SR        2
American     C     SR        3
American     B     GEN       4
Indian       A     SR        1
Indian       A     NR        2
Indian       B     NR        3
Indian       B     NR        4
Indian       B     GEN       5
Italian      C     GEN       1
Italian      A     SR        2
Italian      B     SR        3

Now the above table can be used under Common Table Expression (CTE) and can be easily queried for RowNum = 1. CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT. It is not stored as an object and lasts only for the duration of the query.

Query 2:

WITH MyCte AS
(
SELECT *,row_number() over(partition by NATIONALITY order by Nationality) 
as RowNum
from EMPLOYEE
)
SELECT  NATIONALITY, [TYPE], CATEGORY
FROM    MyCte
WHERE   RowNum =1

The result will be as follows:

NATIONALITY  TYPE  CATEGORY
American     A     NR
Indian       A     SR
Italian      C     GEN
J. Christie
  • 1