Limit row output

Question

I am having some trouble figuring out the correct syntax for what I need to output from my query.

Here is my data (no these are not the correct column names, I'm trying to make it easier to read) The actual table I'm pulling from has 26 columns of data.

employeeNumber - recordNumber - job                - dept - type
 12            - 1            - stapler            - 788  - s
 12            - 6            - paper pusher       - 400  - s
 18            - 2            - phone cleaner      - 600  - p
 18            - 4            - sweeper            - 567  - s
 19            - 0            - typist             - 400  - s
 21            - 0            - mouse ball cleaner - 400  - p

So, here are the selection rules:
If type is P select that record
If employee has no P record, select S
If employee has multiple S records, select the lower record number OR select the record that the Dept is NOT 400 (the 400 row will always be the higher record number)
If there is only one S record, and the Dept is 400 the row should be returned

This is what is currently working:

SELECT employeeNumber, recordNumber, job, dept, type
FROM employees
WHERE (type = 'P')
 OR
 (type = 'S'
            AND employeeNumber NOT IN
                (
                  SELECT employeeNumber
                  FROM employees
                  WHERE type = 'P'
                )
)
ORDER BY employeeNumber, recordNumber

And what I WANT to do is put "limit = 1" (or something similar) at the end, so that in the case of multiple S rows the lower recordNumber will be the row that is returned.

This is the record set that should be returned from the data above:

employeeNumber - recordNumber - job                - dept - type
 12            - 1            - stapler            - 788  - s
 18            - 2            - phone cleaner      - 600  - p
 19            - 0            - typist             - 400  - s
 21            - 0            - mouse ball cleaner - 400  - p

Clear as mud, right?

I keep getting errors. Is there an "easy" way to do it that I'm just missing?

Thanks for any help you can give.

Michelle

score 1 · Answer 1 · answered Dec 29 '11 at 15:50

I don't undestood very good this part:

select the lower record number OR select the record that the Dept is NOT 400 (the 400 row will always be the higher record number)

(If "the 400 row will always be the higher record number" then the left part of the OR is sufficient)

However, this is a point of start.(I dismissed the part after OR)

SELECT * FROM (
    SELECT employeeNumber, recordNumber, job, dept, type
    rank() over (partition by employeeNumber order by type, recordNumber) as rnk
    FROM employees
)
WHERE type = 'P' or (rnk=1 and type=S)

The OR pertaining to the lower record number / not dept 400 was just there for information. You're right, the left is sufficient for it. I'll give your select a try in a bit (I'm knee deep in another problem right now.) — Michelle, Dec 29 '11 at 16:51

score 0 · Answer 2 · answered Dec 30 '11 at 16:22

If your data can't have duplicate TYPE and recordNumber values within an employeNumber, then @Florin's answer works. Otherwise, if there's any chance at all that your ORDER BY in the analytic function would yield two rows with the same rank, you'll get duplicate rows again. I'd use ROW_NUMBER instead, and the WHERE clause can be simplified to simply select all rows with a row number of 1 (which you could do with @Florin's query as well):

CREATE TABLE employees (employeeNumber INTEGER, recordNumber INTEGER
           , job VARCHAR2(100), dept INTEGER, TYPE VARCHAR2(2));

INSERT INTO employees VALUES (12, 6, 'paper pusher', 400, 'S');
INSERT INTO employees VALUES (18, 2, 'phone cleaner', 600, 'P');
INSERT INTO employees VALUES (18, 4, 'sweeper', 567, 'S');
INSERT INTO employees VALUES (19, 0, 'typist', 400, 'S');
INSERT INTO employees VALUES (21, 0, 'mouse ball cleaner', 400, 'P');
INSERT INTO employees VALUES (12, 1, 'stapler', 788, 'S');
INSERT INTO employees VALUES (12, 1, 'stapler2', 654, 'S');

SELECT employeeNumber, recordNumber, job, dept, type
  FROM (SELECT employeeNumber, recordNumber, job, dept, type
             , ROW_NUMBER() 
                 OVER (PARTITION BY employeeNumber 
                           ORDER BY type, recordNumber) rn
          FROM employees)
 WHERE rn = 1;

Yields this:

EMPLOYEENUMBER  RECORDNUMBER JOB                   DEPT TYPE
-------------- ------------- -------------------- ----- ----
            12             1 stapler                788 S
            18             2 phone cleaner          600 P
            19             0 typist                 400 S
            21             0 mouse ball cleaner     400 P

Using RANK with this data would give 2 rows for employee 12. Of course, if this should not happen, perhaps you would want your query to report that fact.

Limit row output

2 Answers2