SQL Query Help

I have a list of Users in the system with User ID's, Application ID they accessed & sequence number for each step.  
Now I want to select a list of the latest applications accessed by user (With Sequence numbers).

Complete List
----------------------------------------------------------------------
|  User#  |    Application#       |     Sequence#    |
|      1       |              4                 |              1             |  
|      1       |              4                 |              2             |  
|      1       |              4                 |              3             |  
|      1       |              5                 |              1             |  
|      1       |              5                 |              2             |  
|      1       |              5                 |              3             |  

|      1       |              9                 |              1             |  
|      1       |              9                 |              2             |  
|      1       |              9                 |              3             |  
|      2       |              7                 |              1             |  
|      2       |              7                 |              2             |  
|      2       |              7                 |              3             |  
|      2       |              3                 |              1             |  
|      2       |              3                 |              2             |  
|      2       |              3                 |              3             |  

----------------------------------------------------------------------


Filtered List needed (Assume that each Application has a time-stamp used for access (We can compare time-stamps and let the latest application accessed for each user):
----------------------------------------------------------------------
|  User#  |    Application#       |     Sequence#    |
|      1       |              4                 |              1             |  
|      1       |              4                 |              2             |  
|      1       |              4                 |              3             |  
|      2       |              3                 |              1             |  
|      2       |              3                 |              2             |  
|      2       |              3                 |              3             |  
|               |                                  |                             |  
----------------------------------------------------------------------


Assume that the fields on the Table are "UserID", "ApplicationID", "SeqID".
The constraint is that I want the second list records in the same List order as the first. If user "1" comes before in the First list then that users records (All grouped records for the application selected) will come before in second list and so on.

Any ideas on how to format the Query?

Please let me know if I am not clear.
ArikkanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
>>"Assume that each Application has a time-stamp used for access"

Where is that timestamp column? on what table? what is  the name of that column?
Why have no timestamps been included in your sample data because it will be critical to success I believe.

What are the names of all tables involved?
Have you provided the real column names?
mankowitzCommented:
The constraint is that I want the second list records in the same List order as the first.

Remember that database rows have no inherent order. If you need a particular order, you have to use an "order by" clause.

Also, what are the bold lines in your diagram? In the first list, you bolded user 1 and application 5, but in your filter, you have user 1 and application 4. The last one in the list is application 9.
ArikkanAuthor Commented:
ok my bad.

[UsersTable]

Select * from [UsersTable] order by UserID,  ApplicationID,  SeqID;  


----------------------------------------------------------------------------------------------
|  UserID  |    ApplicationID     |     SeqID            |     TimeStamp    |
|      1       |              4                 |              1             |          9.00 PM     |
|      1       |              4                 |              2             |          9.00 PM     |  
|      1       |              4                 |              3             |          9.00 PM     |

|      1       |              5                 |              1             |          4.00 PM     |  
|      1       |              5                 |              2             |          4.00 PM     |  
|      1       |              5                 |              3             |          4.00 PM     |  
|      1       |              9                 |              1             |          1.00 PM     |  
|      1       |              9                 |              2             |          1.00 PM     |  
|      1       |              9                 |              3             |          1.00 PM     |  
|      2       |              3                 |              1             |          5.00 PM     |  
|      2       |              3                 |              2             |          5.00 PM     |  
|      2       |              3                 |              3             |          5.00 PM     |  

|      2       |              7                 |              1             |          2.00 PM     |  
|      2       |              7                 |              2             |          2.00 PM     |  
|      2       |              7                 |              3             |          2.00 PM     |  
-----------------------------------------------------------------------------------------------


So what change can I do in the above query where I can add the timestamp and get the below result.

So in this case, we pick the bolded rows as the new list.

-----------------------------------------------------------------------------------------------
|  UserID  |    ApplicationID     |     SeqID            |     TimeStamp    |
|      1       |              4                 |              1             |          9.00 PM     |  
|      1       |              4                 |              2             |          9.00 PM     |  
|      1       |              4                 |              3             |          9.00 PM     |
|      2       |              3                 |              1             |          5.00 PM     |
|      2       |              3                 |              2             |          5.00 PM     |  
|      2       |              3                 |              3             |          5.00 PM     |  
|               |                                  |                             |                             |  
-----------------------------------------------------------------------------------------------
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

PortletPaulEE Topic AdvisorCommented:
SELECT
      ut.*
FROM [UsersTable] AS ut
      INNER JOIN (
            SELECT
                  UserID
                , ApplicationID
                , MAX([TimeStamp]) AS TimeStamp
            FROM [UsersTable]
            GROUP BY
                  UserID
                , ApplicationID
      ) AS l ON ut.UserID = l.UserID
                  AND ut.ApplicationID = l.ApplicationID
                  AND ut.TimeStamp = l.TimeStamp
ORDER BY
      ut.UserID
    , ut.ApplicationID
    , ut.SeqID;

Open in new window

johnsoneSenior Oracle DBACommented:
Why not use an analytic function?  This definitely works in Oracle and as far as I can tell, there is a DENSE_RANK function in SQL Server as well.
SELECT userid, 
       applicationid, 
       seqid, 
       timestamp 
FROM   (SELECT userid, 
               applicationid, 
               seqid, 
               timestamp, 
               Dense_rank() 
                 OVER ( 
                   partition BY userid 
                   ORDER BY timestamp DESC) dr 
        FROM   [UsersTable]) a 
WHERE  dr = 1; 

Open in new window

PortletPaulEE Topic AdvisorCommented:
yes, DENSE_RANK() works in SQL Sever the same way

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.