Solved

SQL: Show Last 2 and First 2 Attributes for a table in a Query

Posted on 2014-04-18
13
429 Views
Last Modified: 2014-04-21
I'm needing to create a query using Oracle SQL that that will display the first two attributes in a table and then the last 2 attributes of a particular table, for example sakes I'll call it the PRODUCT table.

After working on it for a while I know I can use this to get the first 2:

SELECT *
FROM PRODUCT
WHERE ROWNUM <=2;

Open in new window


But how would I make it also show the last 2 within the same query?
0
Comment
Question by:Pancake_Effect
  • 4
  • 3
  • 3
  • +1
13 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 40009920
First rownum is a virtual column and not a good way to limit rows.

Off the top of my head here is what I came up with:
drop table tab1 purge;
create table tab1(col1 char(1));

insert into tab1 values('a');
insert into tab1 values('b');
insert into tab1 values('c');
insert into tab1 values('d');
insert into tab1 values('e');
insert into tab1 values('f');
insert into tab1 values('g');
commit;

select col1 from
(
	select col1,
		row_number() over(order by col1 asc) asc_order,
		row_number() over(order by col1 desc) desc_order
	from tab1
)
where asc_order <=2 or
	desc_order <=2
order by col1
/

Open in new window

0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40009921
To add to the rownum comment, it is also applied to the results before any sorting.

Take the test case I posted above and run:
select rownum, col1 from tab1 order by col1 desc;

Before you run the select, what would you expect?
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 40009938
While not directly related to this question (or maybe it is), since you mentioned in your earlier question today that you're new at SQL, there's something you should definitely be aware of.

Oracle does not guarantee that data is returned in any particular order.

SQL Server, for example, has a concept of a clustered index - which is the physical order of the pages on disk.  Oracle does not have such a concept.

If you really want to ensure that rows are always returned in the same order, always use an ORDER BY:

Reference:
Asktom: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6257473400346237629
Docs: http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF20039
Tom Kyte Blog: http://tkyte.blogspot.com/2005/08/order-in-court.html
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 300 total points
ID: 40010092
Also to add something here as you mentioned

"that will display the first two attributes in a table and then the last 2 attributes"

Attribute should be associated by a qualifier.

then better you  use the qualifier and sort the results with it and display it with the help of rownum as steve suggested in comment  http:#a40009921

ie., for eg:

this table has columns such as min_price(number) or product_id (number) you can get

first 2

select * from (select a.* from products a order by a.min_price )where rownum<=2

last 2

select * from (select a.* from products a order by a.min_price DESC )where rownum<=2

if your qualifier is a product_id then you can get the same by sorting it by product_id column. also depending on the type of sort(asc, desc) you can get the first and last results

if there are nulls in those qualifiers then you have use nvl functions to handle them specially like whether you want them to be considered as 0 or some other value before sorting those nulls.
ie., if min_price has null then you better use like this in where nvl(a.min_price,0)
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40010475
Okay so I took some of the information above and tried inputting it in, however I'm getting a error "DESC_ORDER" invalid identifier. Here is my script plugged in with your guy's provided information:

select	 "ASSIGNMENT"."ASSIGN_NUM" as "ASSIGN_NUM",
	 "ASSIGNMENT"."ASSIGN_DATE" as "ASSIGN_DATE",
	 "ASSIGNMENT"."PROJ_NUM" as "PROJ_NUM",
	 "ASSIGNMENT"."EMP_NUM" as "EMP_NUM",
	 "ASSIGNMENT"."ASSIGN_JOB" as "ASSIGN_JOB",
	 "ASSIGNMENT"."ASSIGN_CHG_HR" as "ASSIGN_CHG_HR",
	"ASSIGNMENT"."ASSIGN_HOURS" as "ASSIGN_HOURS",
	"ASSIGNMENT"."ASSIGN_CHARGE" as "ASSIGN_CHARGE",
	row_number() over(order by ASSIGN_NUM asc) asc_order,
	row_number() over(order by ASSIGN_NUM desc) desc_order
	
 from	 "ASSIGNMENT" "ASSIGNMENT"
where asc_order <=2 or
	desc_order <=2
order by ASSIGN_NUM

Open in new window

0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40010490
I am logging from mobile didnn't see total query copird ur qry
Try something like this
Select * from (Select "ASSIGNMENT"."ASSIGN_NUM" as "ASSIGN_NUM", "ASSIGNMENT"."ASSIGN_DATE" as "ASSIGN_DATE", "ASSIGNMENT"."PROJ_NUM" as "PROJ_NUM", "ASSIGNMENT"."EMP_NUM" as "EMP_NUM", "ASSIGNMENT"."ASSIGN_JOB" as "ASSIGN_JOB", "ASSIGNMENT"."ASSIGN_CHG_HR" as "ASSIGN_CHG_HR", "ASSIGNMENT"."ASSIGN_HOURS" as "ASSIGN_HOURS", "ASSIGNMENT"."ASSIGN_CHARGE" as "ASSIGN_CHARGE" from ASSIGNMENT  order by "ASSIGNMENT"."ASSIGN_NUM") where rownum<=2
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:Steve Wales
ID: 40010496
If you notice Slightwv's reply he's doing a select from an inline view.

Without testing, you're probably going to have to do something like this:

select  ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, EMP_NUM, ASSIGN_JOB, ASSIGN_CHG_HR, ASSIGN_HOURS, ASSIGN_CHARGE
from
(
select	 "ASSIGNMENT"."ASSIGN_NUM" as "ASSIGN_NUM",
	 "ASSIGNMENT"."ASSIGN_DATE" as "ASSIGN_DATE",
	 "ASSIGNMENT"."PROJ_NUM" as "PROJ_NUM",
	 "ASSIGNMENT"."EMP_NUM" as "EMP_NUM",
	 "ASSIGNMENT"."ASSIGN_JOB" as "ASSIGN_JOB",
	 "ASSIGNMENT"."ASSIGN_CHG_HR" as "ASSIGN_CHG_HR",
	"ASSIGNMENT"."ASSIGN_HOURS" as "ASSIGN_HOURS",
	"ASSIGNMENT"."ASSIGN_CHARGE" as "ASSIGN_CHARGE",
	row_number() over(order by ASSIGN_NUM asc) asc_order,
	row_number() over(order by ASSIGN_NUM desc) desc_order
 from	 "ASSIGNMENT" "ASSIGNMENT"
)
where asc_order <=2 or
	desc_order <=2
order by ASSIGN_NUM

Open in new window

                                           
Also, try to avoid the quotes for column names - it enforces case sensitivity on column names.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40010517
Works great! However I personally don't understand the concept very well. Why are there two select & from statements?

EDIT:

I also found this doing my Google search:

select * from (select top(5) * from ASSIGNMENT order by ASSIGN_NUM ASC) a
union
select * from (select top(5) * from ASSIGNMENT order by ASSIGN_NUM DESC) b

Open in new window


Could something like that work as well? Seems pretty simple. But when I run it I get a Missing Expression error for some reason?
0
 
LVL 16

Accepted Solution

by:
Wasim Akram Shaik earned 300 total points
ID: 40010558
if you see my comment. i suggested you a similar approach

select * from (select  * from ASSIGNMENT order by ASSIGN_NUM ASC) a where rownum<3
union
select * from (select  * from ASSIGNMENT order by ASSIGN_NUM DESC) b where rownum<3
0
 
LVL 16

Assisted Solution

by:Wasim Akram Shaik
Wasim Akram Shaik earned 300 total points
ID: 40010562
-->and However I personally don't understand the concept very well.

The concept is very simple,

Step 1

a)

you are selecting all columns from the table by using the wild card *

b)

 you are sorting the results by using a order by clause and specifying the column name on which the sort is to be applied and after doing a sort

Step 2:
you are selecting the top 2 from the table by using the rownum condition from a set of rows which are already sorted in step 1

**EDITED Again, Just re-read, if you are asking why two select in steve's (both wales and light :) ) comment , then also the same justification, however steve had used analytical function(row_number()) to sort the result that's the only difference
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 100 total points
ID: 40010732
OK first off Top N is a SQL Server function - pretty sure Oracle doesn't have a top function.

In order to do that in Oracle you need to use the ROWNUM virtual column.

You can't order by the rownum - rownum is assigned as the rows are returned and before the sort.

So, the inline view gets you the rownums.

Then you select from the inline view so you can sort by them.

Steve Light's original example he gave you uses an analytic function so that you have a 2 extra columns listing 1-n in both ascending and descending order so you can just select top 2 and bottom 2 easily.

Since there is no top n function in Oracle, the way presented is the simplest way to do it, really.

If you don't understand the full query break it down and just run the inner query first and examine the data returned so you can see what's happening.
SQL> select col1,
  2             row_number() over(order by col1 asc) asc_order,
  3             row_number() over(order by col1 desc) desc_order
  4     from tab1;

C  ASC_ORDER DESC_ORDER
- ---------- ----------
g          7          1
f          6          2
e          5          3
d          4          4
c          3          5
b          2          6
a          1          7

7 rows selected.

SQL>

Open in new window


Does that help any better ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40010821
>>however I'm getting a error "DESC_ORDER" invalid identifier

As sjwales pointed out, you need the inline view.

>>if you see my comment. i suggested you a similar approach

I would not use this method.  It accesses the table twice.  Using the analytic functions only hits the table once.  The analytic functions are pretty efficient since they are for use in data warehouse environments.

>> pretty sure Oracle doesn't have a top function.

Not a TOP function but in 12c they are starting to catch up with the rest of the world.

http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_10002.htm#SQLRF55636


1.1.6.7 Native SQL Support for Query Row Limits and Row Offsets

The FETCH FIRST and OFFSET clauses provides native SQL language support to limit the number of rows returned and to specify a starting row for the return set.

Many queries need to limit the number of rows returned or offset the starting row of the results. For example, top-N queries sort their result set and then return only the first n rows. FETCH FIRST and OFFSET simplify syntax and comply with the ANSI SQL standard.
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 40013686
Thanks for all the help guys!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now