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
Messages: 164
Registered: April 1999
Senior Member
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
FROM your_table b
WHERE b.gf = a.gf
AND b.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,
Previous Topic: inserting into nested tables
Next Topic: Column Level Security
Goto Forum:

Current Time: Wed Apr 14 17:45:05 CDT 2021