Solved

Query to be used for row ordering in a table?

Posted on 2011-03-10
3
415 Views
Last Modified: 2012-05-11
Hi,
I have a base table with the data as shown below.

Id                  N                    Date                          
--                  -                     ----                          
256      2      2011-03-01 14:27:35.62        
255      3      2011-03-01 14:26:23.513        
258      2      2011-03-01 14:25:09.477        
259      1      2011-02-28 04:49:26.637        
255      3      2011-02-28 04:40:40.93        
258      2      2011-02-28 04:30:36.343        
254      2      2011-02-25 12:20:38.743        
256      2      2011-02-25 07:37:25.733              
252      3      2011-02-25 07:34:36.533        
255      3      2011-02-25 07:32:02.72        
254      2      2011-02-25 07:30:55.653        
252      3      2011-02-25 07:30:01.457        
252      3      2011-02-25 05:12:46.287    

Is it possible to order the data so that the output looks as shown below?
Id                  N                    Date                          
--                  -                     ----    
256      2      2011-03-01 14:27:35.621
256      2      2011-02-25 07:37:25.7332
255      3      2011-03-01 14:26:23.513
255      3      2011-02-28 04:40:40.93
255      3      2011-02-25 07:32:02.72
258      2      2011-03-01 14:25:09.477
258      2      2011-02-28 04:30:36.343
259      1      2011-02-28 04:49:26.637
254               2                    2011-02-25 12:20:38.743
254      2      2011-02-25 07:30:55.653
252               3                   2011-02-25 07:34:36.533
252      3      2011-02-25 07:30:01.457
252      3      2011-02-25 05:12:46.287

The idea is :-
Take the first ID (256) from the base table and read the no of instance (N).Since it is 2, there is another row with id 256 in the table.Keep the first row in the same place and place the 2nd row below the first row.

Take the next ID(255) and read the no of instance (N).Since it is 3, place the other two rows with id 255 below the first row with id 255.

This process should continue for all the rows in the table.
Please advise on the query to be used.

0
Comment
Question by:phani_82
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35095725
I don't think this sort of ordering is possible just using a query.  You could craft a T-SQL procedure or write a program in some language to accomplish this sort of sorting.
0
 
LVL 13

Accepted Solution

by:
alpmoon earned 500 total points
ID: 35096994
That should give the result set you want:

select a.id, N, Date
from test a, (select id, max(Date) maxD from test group by id) b
where a.id = b.id
order by b.maxD desc, id, Date desc

If you are at an old version not supporting derived tables, you can do it in two steps.
0
 
LVL 26

Expert Comment

by:wilcoxon
ID: 35099258
Good point.  I missed that the date was in descending order (and he just wanted to group ids).  Disregard my comment.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SyBase SQL Syntax 5 679
detecting a database behind a website 3 383
install ASE 16 side by side with ASE 15.7 15 880
sql anywhere query 5 148
Configuring Remote Assistance for use with SCCM
Deploying our service is a grudge match between customer benefits and customer pain. In one corner, rolling out fixes (yay!) and delivering new features (double yay!). In the other corner, training on new features (boo – sounds like work), and chan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question