Prev: Recursive (Instead Of) Trigger
Next: is the Top or Order by done first?
From: jodleren on 22 Apr 2010 11:47
Hi there

In a project being transferred from MS Access to MS SQL, I'd like an
easy replace of First, such as:

select a, first(b), group by a

Normally I can not get B, but I just need one of them (any)
(No I do not know anything about the data yet)

Sonnich
From: Tibor Karaszi on 22 Apr 2010 12:10
As you probably know, there is no order between rows in a table, so first
and last has no meaning. Having said that, and if which row you pick up
doesn't matter, I suggest you use MIN() or MAX().

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi



"jodleren" <sonnich(a)hot.ee> wrote in message
news:8ed821fb-306e-4e09-848e-470872627c59(a)y17g2000yqd.googlegroups.com...
> Hi there
>
> In a project being transferred from MS Access to MS SQL, I'd like an
> easy replace of First, such as:
>
> select a, first(b), group by a
>
> Normally I can not get B, but I just need one of them (any)
> (No I do not know anything about the data yet)
>
> Sonnich

From: --CELKO-- on 22 Apr 2010 13:02
Let me do a cut & paste from the next edition of SQL FOR SMARTIES:
============================

FIRST and LAST functions are non-standard shorthands you will find in
SQL products in various forms. Rather than compute an aggregate value,
they sort a partition on one set of columns, then return an expression
from the first or last row of that sort. The expression usually has
nothing to do with the sorting columns. This is a bit like the joke
about the British Sargent-Major ordering the troops to line up
alphabetically by height. The general syntax is:

[FIRST | LAST](<expr>) OVER (<window specification>)

Using the imaginary Personnel table again:

SELECT emp_id, dept_nbr, hire_date,
FIRST(hire_date)
OVER (PARTITION BY dept_nbr
ORDER BY emp_id)
AS first_hire_by_dept
FROM Personnel;

The results get the hire date for the employee who has the lowest
employee id in each department.

emp_id dept_nbr hire_date first_hire_by_dept
=====================================================
7369 20 '2011-01-01' '2011-01-01' ◄ first emp_id in
dept 20
7566 20 '2011-01-02' '2011-01-01'
7902 20 '2011-01-02' '2011-01-01'
7788 20 '2011-01-04' '2011-01-01'
7876 20 '2011-01-07' '2011-01-01' ◄ last emp_id in dept
20
7499 30 '2011-01-27' '2011-01-27' ◄ first emp_id in
dept 30
7521 30 '2011-01-09' '2011-01-27'
7844 30 '2011-01-17' '2011-01-27'
7654 30 '2011-01-18' '2011-01-27'
7900 30 '2011-01-20' '2011-01-27' ◄ last emp_id in dept
30

If we had used LAST() instead, the two chosen rows would have been:

(7876, 20, '2011-01-07', '2011-01-01')
(7900, 30, '2011-01-20', '2011-01-27')

The Oracle extensions FIRST_VALUE and LAST_VALUE are even stranger.
They allow other ordinal and aggregate functions to be applied to the
retrieved values. If you want to use them, I suggest that you look
product specific references and examples.

You can do these with Standard SQL and a little work. The skeleton

WITH FirstLastQuery
AS
(SELECT emp_id, dept_nbr,
ROW_NUMBER()
OVER (PARTITION BY dept_nbr
ORDER BY emp_id ASC) AS asc_order,
ROW_NUMBER()
OVER (PARTITION BY dept_nbr
ORDER BY emp_id DESC) AS desc_order
FROM Personnel)

SELECT A.emp_id, A.dept_nbr,
OA.hire_date AS first_value,
OD.hire_date AS last_value
FROM FirstLastQuery AS A,
FirstLastQuery AS OA,
FirstLastQuery AS OD
WHERE OD.desc_order = 1
AND OA.asc_order = 1;
 | 
Pages: 1
Prev: Recursive (Instead Of) Trigger
Next: is the Top or Order by done first?