View previous topic :: View next topic
|
Author |
Message |
kishpra
New User
Joined: 24 May 2010 Posts: 92 Location: Pune
|
|
|
|
I have a table with below data
Emp No Text Data Dept Num
1234 Wired duty 001
1234 8283838-10 002
1236 L/E 23-Jan 003
1238 Pipes fitted 001
1236 L/E 01-Feb 004
1234 L/E 01-Feb 004
1234 Testing 002
I want the query output as using ORDER BY
Emp No Text Data Dept Num
1234 L/E 01-Feb 004
1234 8283838-10 002
1234 Testing 002
1234 Wired duty 001
1236 L/E 23-Jan 003
1236 L/E 01-Feb 004
1238 Pipes fitted 001
That is Group of records with same Emp No grouped together. And with the same Emp No, Text Data records beginning with 'L/E' for the same EMp No to be grouped together and for Text Data other than beginning with 'L/E' should be grouped by Dept Num e.g.
1234 8283838-10 002
1234 Testing 002 |
|
Back to top |
|
|
RahulG31
Active User
Joined: 20 Dec 2014 Posts: 446 Location: USA
|
|
|
|
What I can think of, is that you can include a case statement in your query like this:
Quote: |
Select Emp_No, Text_Data,
CASE WHEN SUBSTR(Text_Data,1,3) = 'L/E'
THEN
' ' || Text_Data
ELSE
Text_Data
END AS SOMETHING
,Dept_Num
From MyTable
ORDER BY Emp_No, SOMETHING, Dept_Num
|
Since, you are prefixing Text_Data with a space only when 'L/E', the row containing 'L/E' will always be the first for each Emp_No record set.
*Note that you get 4 columns out of this query.
. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
As you haven't used code tags to present your data nothing lines up. Why should reponders have to 'decode' your post in order to give an answer? Some may not even bother and it could be them that have the definitive answer for you. If you cannot be bothered, why should they? |
|
Back to top |
|
|
|