Solved

Query to be used for row ordering in a table?

Posted on 2011-03-10
3
411 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Knowledge base software has turned out to be a quite reliable method for storing information, promoting collaborative work and for sharing valuable input and solutions.However, some organizations are trying to develop a knowledge base that works wit…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now