1

Topic: Grouping. Than to replace First?

How by means of grouping for each value of column ID1 to select value from column ID2 which corresponds to minimum value from column DateA?
At Access for this purpose there is function FIRST.

SELECT ID1, FIRST (ID2), MIN (DateA) From Table1...

At a SQL Server of such function is not present sad
Instead of FIRST (ID2) to write MIN (ID2) does not approach, as value ID2 which corresponds to minimum date from  DateA, not always the minimum.
Example
Initial data
ID1 ID2 DateA
1 10 1/1/2017
1 12 1/2/2017
2 20 1/4/2017
2 21 1/4/2017
2 8 1/10/2017
Result
ID1 ID2
1 10
2 20

2

Re: Grouping. Than to replace First?

Shovgenyuk wrote:

At a SQL Server of such function is not present sad

but is over

3

Re: Grouping. Than to replace First?

How it is not present? All is .

4

Re: Grouping. Than to replace First?

Gavrilenko Sergey Alekseevich;
SQL SERVER 2008

5

Re: Grouping. Than to replace First?

Shovgenyuk;
Enumerate ROW_NUMBER, leave only lines with number 1.

6

Re: Grouping. Than to replace First?

SELECT TOP (1) WITH TIES *
FROM T
ORDER BY RANK () OVER (PARTITION BY ID1 ORDER BY DateA, ID2);

7

Re: Grouping. Than to replace First?

Variant 1 from iap

WITH t (ID1, ID2, DateA) AS
(
SELECT 1, 10, ' 20170101'
UNION ALL
SELECT 1, 12, ' 20170102'
UNION ALL
SELECT 2, 20, ' 20170104'
UNION ALL
SELECT 2, 21, ' 20170104'
UNION ALL
SELECT 2, 8, ' 20170110'
);
c AS
(SELECT
ROW_NUMBER () OVER (PARTITION BY ID1 ORDER BY DateA, ID2) n;
ID1, ID2, DateA
FROM t
)
SELECT *
FROM c
WHERE n = 1

8

Re: Grouping. Than to replace First?

iap wrote:

SELECT TOP (1) WITH TIES *
FROM T
ORDER BY RANK () OVER (PARTITION BY ID1 ORDER BY DateA, ID2);

and what for sorting on ID2?  "the first entrance" is necessary.

9

Re: Grouping. Than to replace First?

wrote:

it is passed...
And what for sorting on ID2?  "the first entrance" is necessary.

In the same place date identical.
How it is offered to select one or the other?

10

Re: Grouping. Than to replace First?

iap wrote:

As it is offered to select one or the other?

well  "the first entrance" if I correctly understand an essence f-ii FIRST

11

Re: Grouping. Than to replace First?

wrote:

it is passed...
Well  "the first entrance" if I correctly understand an essence f-ii FIRST

What of two first?
In this case if to remove ID2 from ORDER BY RANK () produces all records with identical minimum date.
If one is necessary only, but it is not known what, replace RANK () on ROW_NUMBER ().