how to get limited number of rows from combination of 2 queries

Question

Iam facing some logical issues.Hope that someone can find me a solution to it

Iam having a query

select FirstName||'|'||LastName||to_char(salary) 
from (select FirstName,LastName,salary from subs_tbl where salary=24000) 
union all select FirstName||'|'||''||to_char(salary) 
from (select FirstName,LastName,salary from subs_tbl where salary=30000)

so my result will be

Satish|Kumar|24000

Vimal|Kumar|24000

Dinesh||30000

Let say am having 3 rows as my result but i need to add another condition to the whole query that i need only 2 rows out of the combination of these queries.

Please help me with this

Meherzad · Answer 1 · 2013-02-22T12:37:03.150

1

Try this avoid union's that you have used

SELECT FirstName||'|'||LastName||to_char(salary) 
FROM subs_tbl 
WHERE salary=24000 or salary=30000) 
LIMIT 2

EDIT

SELECT fullname
FROM   (SELECT firstname
           || '|'
           || lastname
           || To_char(salary) AS 'FullName'
    FROM   (SELECT firstname,
                   lastname,
                   salary
            FROM   subs_tbl
            WHERE  salary = 24000)
    UNION ALL
    SELECT firstname
           || '|'
           || ''
           || To_char(salary) AS 'FullName'
    FROM   (SELECT firstname,
                   lastname,
                   salary
            FROM   subs_tbl
            WHERE  salary = 30000))
WHERE  rownum <= 5;

Hope this helps

edited Feb 22 '13 at 12:37

answered Feb 22 '13 at 09:49

Meherzad

8,433
1
30
40

i cant use this coz my requirement is the resultant of both the queries results together that's why am using UNION all function as seen from the select part its different for both the queries which i have joined select FirstName||'|'||LastName||to_char(salary) and select FirstName||'|'||''||to_char(salary) – Meera Feb 22 '13 at 11:06
This will also give you combined records only. Do you need random records from the result or top 2 ? – Meherzad Feb 22 '13 at 11:07
The thing is my select clause in both are different in first am concatinating FirstName|LastName|Salary and in second query am concatinating FirstName||Salary, here the LastName should not be displayed and the query should return in the firstway only if salary is 24000 and query should return second way only if salary is 30000 – Meera Feb 22 '13 at 11:42
You need 1 from each group of 24k and 30k ? If so then add LIMIT 1 to each of your union – Meherzad Feb 22 '13 at 11:46
its not that i need one from each the limit value is dynamic it can be any value as stated in the example Satish|Kumar|24000 Vimal|Kumar|24000 Dinesh||30000 if the query returns these then i need only the first two out of it.It need not be 2 like i said earlier it can be any number – Meera Feb 22 '13 at 11:51
is there someway in which this query can be made – Meera Feb 22 '13 at 12:05
Try this if it helps you http://stackoverflow.com/questions/470542/how-do-i-limit-the-number-of-rows-returned-by-an-oracle-query-after-ordering – Meherzad Feb 22 '13 at 12:28

score 1 · Answer 2 · answered Feb 22 '13 at 09:51

1

select FirstName||'|'||LastName||to_char(salary) 
from (select FirstName,LastName from subs_tbl where salary=24000) 
union all select FirstName||'|'||''||to_char(salary) 
from (select FirstName,LastName from subs_tbl where salary=30000) limit 0,2

answered Feb 22 '13 at 09:51

user1187

2,116
8
41
74

i tried out this suggestion by am getting an error 1001: Syntax error in SQL statement before or at: "0", character position: 185 and also the number of rows to which i need to limit it is a dynamic value which will be passed to the procedure am using. Note: Am using Oracle timesten to excecute it. – Meera Feb 22 '13 at 10:51
when the salary is 24000 then i need to display Satish|Kumar|24000 Vimal|Kumar|24000 firstname,lastname and salary but when the salary is 30000 then i need to display Dinesh||30000 firstname then a blank space in between the pipe and the salary so i need the combined result of both of these queries and thats why Iam using UNION ALL – Meera Feb 22 '13 at 11:39

how to get limited number of rows from combination of 2 queries

2 Answers2