Home » RDBMS Server » Server Administration » Duplicate Rows
Duplicate Rows [message #370669] Tue, 18 January 2000 14:35 Go to next message
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 Go to previous message
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
Previous Topic: inserting into nested tables
Next Topic: Column Level Security
Goto Forum:
  


Current Time: Thu Mar 28 16:19:31 CDT 2024