SQL Query Help

Arikkan
Arikkan used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"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?
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.

Author

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     |  
|               |                                  |                             |                             |  
-----------------------------------------------------------------------------------------------
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
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 DBA
Commented:
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

EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
yes, DENSE_RANK() works in SQL Sever the same way

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial