Solved

Query to be used for row ordering in a table?

Posted on 2011-03-10
3
414 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

Enabling the Skype for Business Meeting Scheduler in Hybrid OWA
Facebook has became the #1 social media platform. People share many funny videos there, yet you don't know how to download them? Now you can download Videos from Facebook in just 3 simple steps.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

740 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