CODEWITHSUNDEEP
phpsqlsql-order-by
Kraken
Kraken

Reputation: 24213

Ordering the SQL query in a particular order

Say i have a table Guest and it has column g_id : values 1 to 10.

Now i want the query to return me the g_id's neither in ascending order nor in descending.. but i want the 4th then 3rd and then 5th entry, in this particular order. Also i want just the 4th 3rd and 5th entry.

say my entries have an id and a name . ;i.e. my table Guest has these two tables. Now my table is as following.

1 A
2 B
3 C
4 D
5 E
6 F
7 G
8 H
9 I
10 J

Now i want just the entry with 4th 3rd and 5th g_id, and in this particular order.

How do i write the SQL query? Thanks.

Select * from Guest ___________???

Kindly fill in the gaps.

Upvotes: 1

Views: 765

Answers (6)

horatio
horatio

Reputation: 1436

This query will get you the 3rd, 5th, and 4th items (limit 2, 1 means "retrieve starting with 3rd item, with total number retrieved = 1 records)

(select g_id from Guest limit 2,1)
UNION (select g_id from Guest limit 4,1
UNION (select g_id from Guest limit 3,1)

Upvotes: 0

Torindo Nesci
Torindo Nesci

Reputation: 1

I think that the answer mostly depends on the DBMS you are working on.

In Oracle the query below, even though inefficient, should work

select * from 
    (select * , rownum as order from guest order by id asc ) b 
where b.order = 4
UNION
select * from 
    (select * , rownum as order from guest order by id asc ) b 
where b.order = 3
UNION
select * from 
    (select * , rownum as order from guest order by id asc ) b 
where b.order = 5

Not sure if something of more efficient is possible with a simple query, i would use the monster above only and only if the table you are querying is very small.

You also have another option if the table is big and you have to extract only the first rows. In the case you described, I would retrieve the first 5 rows and then programmatically I would extract the rows in position 4,3,5.

you can extract the first 5 rows with this query in oracle

select * from guest order by id asc where rownum < 6

Upvotes: 0

Explosion Pills
Explosion Pills

Reputation: 191729

I'm not sure how set your ordering will be, but you can order by specifics:

ORDER BY
   g_id = 4 DESC,
   g_id = 3 DESC,
   g_id = 5 DESC

You may be better off selecting the entries as they are and doing something like this in your php code:

$order = array('4 ', '3 ', '5 ');
$data = array();
while ($row = $result->fetch()) {
   $data["$row->g_id "] = $row;
}
$data = array_merge(array_flip($order), $data);

Upvotes: 0

fge
fge

Reputation: 121712

One solution is the case statement:

select g_id from (
    select g_id, case g_id
       when 4 then 1
       when 3 then 2
       when 5 then 3
       else 0
    end virtcol
    where virtcol != 0
    order by virtcol
);

Upvotes: 0

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can use a CASE statement in your ORDER BY to use a fake column to sort on and a WHERE IN clause to only return the values you need.

SELECT * 
FROM   Guest
WHERE  g_id IN (3, 4, 5)
ORDER BY
       CASE WHEN g_id = 4 THEN 1
            WHEN g_id = 3 THEN 2
            WHEN g_id = 5 THEN 3
       END

Upvotes: 2

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171391

What is the order that deteremines whether something is 4th, 3rd or 5th? Without an ORDER BY clause, the data is returned in an indeterminate order by SQL. You cannot rely on the order that rows are entered or stored in the database table itself.

You can hard-code what you are asking like this:

select *
from Guest
order by case 
    when g_id = 4 then 1
    when g_id = 3 then 2
    when g_id = 5 then 3
    else 4
end

Upvotes: 2

Related Questions