to fetch first record

[sangram.0149]

hi,
can someone plz help me on this one
i need to fetch the first record from every group of records with the
same emp id.
i cannot use group by because i want to fetch all the fields
corresponding to a particular empid.
plz suggest a solution for this one

regards,
Sangram

[Hardy]

can you post some talbe definition and data?

[Hardy]

try db2 olap function rank/row_number over(partition by ...)

[Hardy]

as a example, first double or triple your staff table in sample
database.

then run:

with data as
(
select row_number() over (partition by id) as num, id, name from staff
)
select id, name from data where num = 1
;

is this way your want?

[sangram.0149]

thx mate
i got it

[Brian Tkatch]

>i cannot use group by because i want to fetch all the fields

correspondin g to a particular empid

Yes you can. :)

SELECT * FROM table WHERE (empid, col2) IN
(SELECT empid, MIN(col2) FROM TABLE GROUP BY empid)
B.

[Chris]

Brian,

Depending on the data in the table, that is more akin to RANK than
ROW_NUMBER. That is, you might get multiple rows for each empid using
that method - if there are mutliple rows with the same MIN(col2) for a
given empid.

Of course, if this isn't the case, then it works just fine.

-Chris

[Brian Tkatch]

I thought of the same thing. :)

However, he said he couldn't use GROUP BY because he wanted the rest of
the records too. From that i inferred that had he only wanted one
column or so, GROUP BY would work. Therefore, i provided my answer.

B.

[Knut Stolze]

Brian Tkatch wrote:

i cannot use group by because i want to fetch all the fields
correspondi ng to a particular empid

Yes you can. :)

SELECT * FROM table WHERE (empid, col2) IN
(SELECT empid, MIN(col2) FROM TABLE GROUP BY empid)

But you _can_ also do without the GROUP BY. For example like here:

Data:
-----
$ db2 "select * from t1"

A B
----------- -----------
1 2
1 3
1 4
1 1
2 1
2 2
2 8

This will now get the first two rows for each group:

SELECT o.*
FROM t1 AS o,
LATERAL ( SELECT *
FROM t1 AS i
WHERE o.a = i.a
ORDER BY i.b
FETCH FIRST 2 ROWS ONLY ) AS x
WHERE x.b = o.b

A B
----------- -----------
1 2
1 3
2 1
2 2

4 record(s) selected.

Granted, the GROUP BY is much, much nicer. But I just remembered that Serge
mentioned LATERAL once and I thought this should work as well. ;-)

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Brian Tkatch]

OK, another word i need to go look up. :)

Yeah, GROUP BY is much nicer. And, once someone actually understands
GROUP BY (easy conceot, but not taught well) it makes the reason the
statement does what it does very clear.

B.