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

Mar 23 '06 #1

Hardy

can you post some talbe definition and data?

Mar 23 '06 #2

Hardy

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

Mar 23 '06 #3

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?

Mar 23 '06 #4

sangram.0149

thx mate
i got it

Mar 23 '06 #5

Brian Tkatch

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

corresponding to a particular empid

Yes you can. :)

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

Mar 23 '06 #6

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

Mar 23 '06 #7

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.

Mar 23 '06 #8

Knut Stolze

Brian Tkatch wrote:

i cannot use group by because i want to fetch all the fields
corresponding 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

Mar 23 '06 #9

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.

Mar 23 '06 #10

Chris

Knut,

Trying this out myself (because I couldn't understand the results you
posted), I get a slightly different results:

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

Was this just a copying error, or is it really returning different
results for you?

-Chris

Mar 23 '06 #11

Knut Stolze

Chris wrote:

Knut,

Trying this out myself (because I couldn't understand the results you
posted), I get a slightly different results:

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

Was this just a copying error, or is it really returning different
results for you?

Your results are correct. When I first run the query (and copied the
results), I didn't have a (1, 1) row in there. This I added afterwards and
put it as sample data above the results. My fault.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

Mar 24 '06 #12

Andrey Odegov

Sangram,

would it be suitable for you?
(but perhaps too late ;)

SELECT DISTINCT B.*
FROM (SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 8) AS B(c1, c2)
WHERE NOT EXISTS(SELECT *
FROM (SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 8) AS P(c1, c2)
WHERE P.c1 = B.c1
AND P.c2 > B.c2)
---
Andrey Odegov
av******@yandex.ru
(remove GOV to respond)

Mar 30 '06 #13

Serge Rielau

Andrey Odegov wrote:

Sangram,

would it be suitable for you?
(but perhaps too late ;)

SELECT DISTINCT B.*
FROM (SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 8) AS B(c1, c2)
WHERE NOT EXISTS(SELECT *
FROM (SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 8) AS P(c1, c2)
WHERE P.c1 = B.c1
AND P.c2 > B.c2)

FY:
(SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 1, 4 UNION ALL
SELECT 1, 1 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, 2 UNION ALL
SELECT 2, 8) AS B(c1, c2)
Is SQL server syntax. SQL Standard syntax is:
(VALUES (1, 2),
(1, 3),
(1, 1),
(2, 1),
(2, 2),
(2, 8)) AS B(c1, c2)
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Mar 30 '06 #14

Andrey Odegov

Thanx Serge
i got it
i will improve in future :)
---
Andrey Odegov
av******@yandex.ru
(remove GOV to respond)

Mar 31 '06 #15