By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

to fetch first record

P: n/a
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
Share this Question
Share on Google+
14 Replies


P: n/a
can you post some talbe definition and data?

Mar 23 '06 #2

P: n/a
try db2 olap function rank/row_number over(partition by ...)

Mar 23 '06 #3

P: n/a
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

P: n/a
thx mate
i got it

Mar 23 '06 #5

P: n/a
>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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

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

Mar 31 '06 #15

This discussion thread is closed

Replies have been disabled for this discussion.