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!
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
>>"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?
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.
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 |
| | | | |
--------------------------
[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 |
| | | | |
--------------------------
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;
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;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial