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

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?
LVL 4
Pancake_EffectAsked:
Who is Participating?
 
Wasim Akram ShaikConnect With a Mentor Commented:
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
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
Steve WalesSenior Database AdministratorCommented:
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
 
Wasim Akram ShaikConnect With a Mentor Commented:
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
 
Pancake_EffectAuthor Commented:
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
 
Wasim Akram ShaikCommented:
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
 
Steve WalesSenior Database AdministratorCommented:
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
 
Pancake_EffectAuthor Commented:
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
 
Wasim Akram ShaikConnect With a Mentor Commented:
-->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
 
Steve WalesConnect With a Mentor Senior Database AdministratorCommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Pancake_EffectAuthor Commented:
Thanks for all the help guys!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.