Oracle SQL: Limiting multiple where clauses

Question

Apologies if this seems like a duplicate to this question but I believe my use case is slightly different.

I have two tables.

Table1

ID                  INTCODE
-----------------------------
000019827364        1
000019829201        2
890418392101        3
890418390395        4
890418398677        5
505586578932        6
505586578914        7
505586578933        8
505586578012        9
490201827383        10
490201827466        11
001952046578        12


Table2

INTCODE     Category
-------------------------
1           Display
2           Display
3           Display
4           Display
5           Display
6           Audio
7           Audio
8           Audio
9           Audio
10          Audio
11          Audio
12          Audio

My expected query results are all possible 5 digit prefixes of each category and in each of these prefixes - I want to extract at least 2 full IDs. Below is an example if I had a where clause for category as 'Display'.

ID PREFIX       Category    ID  
----------------------------------------------- 
00001           Display     000019827364
00001           Display     000019829201
89041           Display     890418392101
89041           Display     890418390395

The query I currently have is

SELECT
    SUBSTR(t1.ID, 1, 5)
FROM
    table1 t1
    ,table2 t2
WHERE
    AND UPPER(t2.category) = 'DISPLAY'
    AND t2.REGION_ID = 1
    AND t2.ZONE_ID = 2
    AND t1.REGION_ID = 1
    AND t1.ZONE_ID = 2
    AND t1.INTCODE = t2.INTCODE
GROUP BY
    SUBSTR(t1.ID, 1, 5)

I am now kind of lost. Should I be running another query where I say

t1.ID LIKE '00001%'
OR LIKE '89041%'

This list will go on to be huge cause some of the categories have 400-500 prefixes. Is there a better way to go about this? Possibly in a single query?

I'm using Oracle SQL.

Many thanks!

My query results should have the prefixes with the category and also 2 full IDs. I have an example of how my results should look like — KayCee, Feb 17 '15 at 03:22
@Jean-FrançoisSavard: I'm fairly new to SQL, Just started about a month ago. Can you please help me with an example? Thank you! — KayCee, Feb 17 '15 at 03:25

score 1 · Accepted Answer · answered Feb 17 '15 at 03:27

1

You can use row_number() for this:

select Category, ID, IDPrefix
from (select Category, ID, SUBSTR(ID, 1, 5) as IDPREFIX,
             ROW_NUMBER() OVER (PARTITION BY SUBSTR(ID, 1, 5) ORDER BY ID) as seqnum
      FROM table1 JOIN
           table2 t2
           ON t1.INTCODE = t2.INTCODE ANd
              t1.Region_id = t2.Region_id and
              t1.zone_id = t2.zone_id
      WHERE UPPER(t2.category) = 'DISPLAY'
     ) t
WHERE seqnum <= 2;

answered Feb 17 '15 at 03:27

Gordon Linoff

1,242,037
58
646
786

Thank you @Gordon Linoff! I am now reading up about how rownum and partition work :) – KayCee Feb 18 '15 at 06:05

score 0 · Answer 2 · answered Feb 17 '15 at 03:28

Assuming you can to display two rows with different Id, without any more constraint, you could simply use an union where the first query would select the max id, and the second query the min id.

So your query would look something like this

select id_prefix, category, max(id)
from yourTable
union
select id_prefix, category, min(id)
from yourTable

Now simply add to this algorithm your where conditions.

Oracle SQL: Limiting multiple where clauses

2 Answers2