Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query to be used for row ordering in a table?

Posted on 2011-03-10
3
Medium Priority
?
417 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 27

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 2000 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 27

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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
What we learned in Webroot's webinar on multi-vector protection.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 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