0

Say I have the following Person table:

ID, Name, Age, Height, Weight

I do the following query:

SELECT * FROM Person WHERE name = 'Fred' AND age = '19' AND ROWNUM <= 1

This will return the 1st row that matches those criteria.

Now I do the following query:


SELECT * FROM Person WHERE name || '|' age IN ('Fred|19','Joe|25','Sam|21')

This will return all rows that match the criteria.

My question is is there a way to combine the "rownum" functionality of the 1st query with the multi criteria of the 2nd query. I want to get the 1st result of Fred|19 (if any), 1st result of Joe|25 (if any) and 1st result of Sam|21 (if any)

Simply combining these won't work:

SELECT * FROM Person WHERE name || '|' age IN ('Fred|19','Joe|25','Sam|21') AND ROWNUM <= 3

If there are 3+ rows with Fred|19, I will get those 3 rows back. I'm using Oracle database if that helps

MT0
  • 143,790
  • 11
  • 59
  • 117
Jamie
  • 31
  • 1
  • 3
  • `SELECT * FROM Person WHERE name = 'Fred' AND age = '19' AND ROWNUM <= 1 union all SELECT * FROM Person WHERE name = 'Joe AND age = '25' AND ROWNUM <= 1 union all SELECT * FROM Person WHERE name = 'Sam' AND age = '21' AND ROWNUM <= 1` – Bryan Dellinger Oct 17 '22 at 23:42
  • I have already thought of that, I'm looking for a more compact way to do it for larger requests – Jamie Oct 17 '22 at 23:48

1 Answers1

1

It would be better to avoid concatenation in such cases. Try this one:

DBFiddle: https://dbfiddle.uk/7xZ5vyc_

SELECT
  p.*
FROM Person p
WHERE 
  (name,age) IN (
     ('Fred',19),
     ('Joe',25),
     ('Sam',21)
   )
order by row_number()over(partition by name,age order by null) 
fetch first 1 row with ties
Sayan Malakshinov
  • 8,492
  • 1
  • 19
  • 27
  • This works, is it possible to modify the query so it comes in the order of the searches – Jamie Oct 18 '22 at 18:54
  • @Jamie what do you mean? – Sayan Malakshinov Oct 18 '22 at 22:56
  • For example, I execute the query in your answer, one possible result is I get back the first result of Joe, then Sam, then Fred. The desired result is the first row should be first result of Fred, then second row Joe, and third row Sam because that was the order in the query – Jamie Oct 18 '22 at 23:09
  • @Jamie ah, in this case you need to specify ordering. For example: https://dbfiddle.uk/Z-To5rwy – Sayan Malakshinov Oct 18 '22 at 23:43