Getting the lowest record for Oracle

Question

I'm having a problem with selecting data from my table, where in I'm trying to select a group of records that have some fields with duplicate values, I want to select the ones with the lowest value, however since I'm fairly new to SQL, I'm at a dead end right now on what syntax should I use to get only the lowest values.

Example

SEQ_NO     ID_NO     
01          1990
02          1990
03          1991
05          1890
08          1890
01          1992

I only want to select all the records with low SEQ_NO, so I should get all of the records from 1990 to 1992, but only each ID_NO has only the lowest SEQ_NO.

What syntax should I use to remove the unnecessary records (for example, remove the SEQ_NO for 1990)?

http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql — Kanagavelu Sugumar, Jan 19 '13 at 14:59
@KanagaveluSugumar you should just posted the answer instead :) — bonCodigo, Jan 19 '13 at 16:14

score 2 · Accepted Answer · answered Jan 19 '13 at 14:55

2

use MIN, which is an AGGREGATE FUNCTION and a GROUP BY clause.

SELECT  ID_NO, MIN(SEQ_NO) LowestVALUE
FROM    Table1
WHERE   ID_NO BETWEEN 1990 AND 1992
GROUP   BY ID_NO

SQLFiddle Demo

answered Jan 19 '13 at 14:55

John Woo

258,903
69
498
492

Getting the lowest record for Oracle

1 Answers1