SQL Server group by status query problem

Question

Table temp

ID  Status
1   completed
2   approved
3   paid
4   cancel
5   approved
6   paid

I want to display above recored as per bellow table without using union.because this is client requirement, so please provide help for this.

Table temp

ID  Status
1   completed
2   approved
5   approved
3   paid
6   paid
4   cancel

Answer 1

Almost identical to Dan's answer, but for SQL Server (Or any standards SQL database system):

SELECT 
  ID, 
  Status 
FROM 
  temp 
ORDER BY 
  CASE Status
      WHEN 'completed' THEN 1
      WHEN 'approved' THEN 2
      WHEN 'paid' THEN 3
      WHEN 'cancel' THEN 4
  END

---

Other concerns -

1) The title of this question appears to be misleading, since it's not about GROUP BY at all, and,

2) I think you have a misconception about UNION also - there's no guarantee of the order in which results will return from a UNION or UNION ALL - In the UNION case, there's likely to be a sort operation to help the server to eliminate duplicates, but which sort is performed is entirely up to the server and you shouldn't rely on such a sort always being necessary. For a UNION ALL, imagine if the first query is a large query, requiring a lot of I/O, and the second query is trivial (say, all of the data required is already in memory). An obvious optimization for the server is to return the results for the second query whilst it's still performing I/O for the first.

Answer 2

Since you are trying to order the rows arbitrarily, you can use a CASE statement in the ORDER BY clause. Here's a working example that will give you the output you're looking for (for SQL Server):

DECLARE @myTable AS TABLE([ID] INT, [Status] VARCHAR(16))
INSERT INTO @myTable VALUES(1, 'completed')
INSERT INTO @myTable VALUES(2, 'approved')
INSERT INTO @myTable VALUES(3, 'paid')
INSERT INTO @myTable VALUES(4, 'cancel')
INSERT INTO @myTable VALUES(5, 'approved')
INSERT INTO @myTable VALUES(6, 'paid')

SELECT * 
FROM @myTable
ORDER BY
    CASE [ID]
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 4
        WHEN 4 THEN 5
        WHEN 5 THEN 3
        WHEN 6 THEN 6
        ELSE 999
    END