Home » SQL & PL/SQL » SQL & PL/SQL » Query needed
Query needed [message #220339] Tue, 20 February 2007 05:52 Go to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

Dear Friend,.

This is my table Structure.

Emp:

Name dept salary
Clement Analayst $200
Louis Admin $230

Emp2:

Name dept salary
Clement Analayst $200
abraham tester $150


Emp3:

Name dept salary
Clement Analayst $200
John Programmer $500


I want first record of each table.. pls..

Kiss07
Re: Query needed [message #220343 is a reply to message #220339] Tue, 20 February 2007 05:55 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you doing your homework? You posted four non-related, basic questions within 4 minutes.
Please read the sticky (first topic of this forum), especially the part about showing what you tried so far..
Re: Query needed [message #222448 is a reply to message #220339] Sun, 04 March 2007 21:15 Go to previous messageGo to next message
srirama46
Messages: 1
Registered: March 2007
Junior Member
select * from emp1 where rownum=1;
Re: Query needed [message #222495 is a reply to message #222448] Mon, 05 March 2007 03:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, that will get you one row, at random.
It's a start, I suppose.
Re: Query needed [message #222508 is a reply to message #222495] Mon, 05 March 2007 04:34 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
Hi,

select * from emp1 where rownum=1
union
select * from emp2 where rownum=1
union
select * from emp3 where rownum=1


Thanks,
srinivas
Re: Query needed [message #222511 is a reply to message #222508] Mon, 05 March 2007 04:37 Go to previous messageGo to next message
pavuluri
Messages: 247
Registered: January 2007
Senior Member
sorry
see this
select * from emp1 where rownum=1
union all
select * from emp2 where rownum=1
union all
select * from emp3 where rownum=1

Thanks,
srinivas
Re: Query needed [message #222532 is a reply to message #222511] Mon, 05 March 2007 05:34 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Excellent. We have now progressed from getting a row from one table at random to getting rows from 3 tables at random, and presenting them as one result set.

In case anyone doesn't know what I'm talking about, let me explain.

(From this Link)
Quote:
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.

So, if you just do a select ... where rownum=1 then you are just getting back a row. It's not the first row entered into the table. Its not guaranteed to be the same row that you got last time you ran the query, even if the data hasn't changed.

The piece of information we are missing for this question is: How do we identify the 'first' record. Is it the record with the lowest salary, the first name (ordered alphabetically), the highest salary, or what.

Once we've got that, you can write a query like this:
SELECT * 
FROM (SELECT *
      FROM   emp1
      ORDER BY <order by clause>)
WHERE rownum=1;

This will get the first row in a given order, which is a distinct improvement.
There are more efficient ways of doing this, but we'll leave that for another question.
Previous Topic: group by query
Next Topic: Sub Query with IN
Goto Forum:
  


Current Time: Sat Dec 03 19:51:37 CST 2016

Total time taken to generate the page: 0.07958 seconds