| Duplicate Rows [message #370669] |
Tue, 18 January 2000 14:35  |
Dan Matsuda
Messages: 1
Registered: January 2000
|
Junior Member |
|
|
|
Hi, I've been trying hard to eliminate duplicate rows in a query. The problem is that I do get one row, but using the MAX function for most fields, I get some wrong field data returned - meaning that I get the particular record wanted, but for some reason there is one field that comes back with the data from the previous of next record. Is there another way of eliminating duplicates?
|
|
|
|
| Re: Duplicate Rows [message #370687 is a reply to message #370669] |
Wed, 19 January 2000 07:14  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Dan,
I'll assume your table has no field where the values are unique (otherwise you could just select where the value of only that field was max for your group criteria) and that you actually want to return data from a single record, rather than the max value for each field grouped by a given field. One possibility is
SELECT gf, mf, of1, of2, of3, of4
FROM your_table a
WHERE ROWNUM =
( SELECT MAX(ROWNUM)
FROM your_table b
WHERE b.gf = a.gf
AND b.mf =
( SELECT MAX(mf)
FROM your_table c
WHERE c.gf = a.mf) );
In this GF is the field you wish to group on, MF is the field you want the max value of, and OFn are all other fields you want returned. It will return a single row for each GF and the row with the max value for MF, if there are multiple rows for with the same MF for a given GF, it will return the one with the highest row number.
Hope this helps,
Paul
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
