asked on

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.

PortletPaul

>>"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?

mankowitz

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.

Arikkan

ASKER

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

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial