Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 420
  • Last Modified:

Query to be used for row ordering in a table?

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
phani_82
Asked:
phani_82
  • 2
1 Solution
 
wilcoxonCommented:
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
 
alpmoonCommented:
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
 
wilcoxonCommented:
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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now