|
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
| |
Share this Question
|
P: n/a
|
can you post some talbe definition and data?
| |
|
P: n/a
|
try db2 olap function rank/row_number over(partition by ...)
| |
|
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?
| |
|
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. | |
|
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
| |
|
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.
| |
|
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 | |
|
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.
| |
|
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
| |
|
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 | |
|
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) | |
|
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 | |
|
P: n/a
|
Thanx Serge
i got it
i will improve in future :)
---
Andrey Odegov av******@yandex.ru
(remove GOV to respond) | | This discussion thread is closed Replies have been disabled for this discussion. | |  Question stats - viewed: 15541
- replies: 14
- date asked: Mar 23 '06
|
