0

I am fetching all stations which belong to a station group from my database. SELECT * FROM stations WHERE station_group_id = 1.

Now, from all the fetched results, I want certain ones to appear first (e.g. the stations which have line_id = 2 to appear first). For example, if this is my stations table:

id | station_group_id | line_id
-------------------------------
1  |                1 |       1
2  |                1 |       2
3  |                1 |       3

I would like the output to be:

id | station_group_id | line_id
-------------------------------
1  |                1 |       2
2  |                1 |       1
3  |                1 |       3

So that line_id = 2 is the first record in the output.

I thought about using ORDER BY, but it isn't quite an order issue, it is more a "preference" one.

So, is it possible to place some records on top of the output, based on a condition, preferably in one query? Thanks!

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
linkyndy
  • 17,038
  • 20
  • 114
  • 194
  • possible duplicate of [Ordering by the order of values in a SQL IN() clause](http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause) – OMG Ponies Feb 19 '12 at 22:00
  • It is not quite a duplicate. I need to order by letting only certain records on top of the output, not using the order from a previous query. – linkyndy Feb 19 '12 at 22:28

3 Answers3

1
SELECT * FROM stations WHERE station_group_id = 1 and line_id = 2
union 
SELECT * FROM stations WHERE station_group_id = 1 and 
         line_id != 2 order by line_id asc
Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
  • I thought about unions, but I'll go for the other answer. Vote up for a useful answer, though. – linkyndy Feb 19 '12 at 22:32
  • or, you can assign line_id = 2 to the record with id=1 and vice versa. then you can run a simple select with "order by". ;) – Nir Alfasi Feb 20 '12 at 02:34
  • Id's and number of records to move on top are random, so I wanted something cleaner :) – linkyndy Feb 20 '12 at 17:06
  • if you want a more generalized solution you should add a column to the table called "display_order". – Nir Alfasi Feb 20 '12 at 17:43
  • And what will be the use of it? I'll still have to iterate all over the table to populate this field based on the current query. And, as I said, the order is dynamic, so it changes from one query to another, so the previous order will be useless in the current query. – linkyndy Feb 20 '12 at 18:51
  • @linkyndy sorry - I didn't notice that you said you wanted to sort it dynamically. how will you know, during run time, to use a condition like: "ORDER BY if(line_id in('2','X','Y','Z'),0,1)" - the "2" is hardcoded, no ? – Nir Alfasi Feb 20 '12 at 18:57
  • I am using an array with the desired `line_id`s in that `if()` clause. – linkyndy Feb 21 '12 at 15:06
  • An array - meaning hardcoded solution (it's not "really" dynamic). If it's going to stay like this forever then I guess that it's totally fine, but if the values in this array might change in the future - then you'll need to test+deploy code changes every time instead of changing fields in a table (display_id). – Nir Alfasi Feb 21 '12 at 16:43
  • Why should an array necessarily be hardcoded? It is generated upon data from the current query. Anyway, my implementation of the accepted answer works fine and I also voted up your answer because it is useful :) – linkyndy Feb 21 '12 at 16:47
  • @linkyndy - it's really not about the upvote or about the "accept". You're missing the point: all I'm saying is that if you know in advance the values then it's not dynamic and you can build a generic solution that will not require code-changes even if values will change in the future. – Nir Alfasi Feb 21 '12 at 17:11
  • Before querying my database, I have an array with the line_ids which should be moved on top of the results. That is the array I use in the if() clause and returns the desired result set. It's least I can do for it to work, adding and modifying an extra field in the table for each query requires more work to be done. I hope I made my point clear (and also that understood yours). – linkyndy Feb 21 '12 at 18:17
1

Try Below:

SELECT * FROM stations 
WHERE station_group_id = 1 
ORDER BY if(line_id in('2','X','Y','Z'),0,1)
0

As you are saying, it is actually a preference, so you should either model it as an extra field on the table (e.g. ordinal, or order, or preferredOrder), or you keep sorting by line_id, and do the "special sort" in code. (find element with id=2, move to top)

mindandmedia
  • 6,800
  • 1
  • 24
  • 33