SQL Issue

[DCypher]

I have a table like shown below, we'll call it "Transactions." EmployeeID is just a unique identifier for an employee, TransNumber is the number of transactions they have handled in a week, and StoreID is the ID number of the store that the employee works at. How can I write a query that pulls the most successful employee for each store. For example, in Store 1, Employee 2 has had 20 transactions while employee 1 has only had 10.


EmployeeId TransNumber StoreID
1 10 1
2 20 1
3 30 2
4 20 2
5 30 3
6 50 3


Thanks for your help!

 

[Pacemaker]

What version of SQL are you using? i.e. MSSQL, MYSQL etc.

 

[DCypher]

Microsoft SQL Server

 

[KB]

I have done this all too many times and always forget how at first. But its something like:


SELECT table.StoreID, table.EmployeeID, table.TransNumber
FROM table
INNER JOIN
(SELECT StoreID, MAX(TransNumber) As Trans
FROM table
GROUP BY StoreID) T ON T.StoreID = Table.StoreID AND T.Trans = table.TransNumber

 

[Pacemaker]

Originally posted by: KB
I have done this all too many times and always forget how at first. But its something like:


SELECT table.StoreID, table.EmployeeID, table.TransNumber
FROM table
INNER JOIN
(SELECT StoreID, MAX(TransNumber) As Trans
FROM table
GROUP BY StoreID) T ON T.StoreID = Table.StoreID AND T.Trans = table.TransNumber

Sorry for not replying sooner, but I got busy. This is similar to what I was going to suggest. I tested it and it works.

 

[brandonbull]

Originally posted by: KB
I have done this all too many times and always forget how at first. But its something like:


SELECT table.StoreID, table.EmployeeID, table.TransNumber
FROM table
INNER JOIN
(SELECT StoreID, MAX(TransNumber) As Trans
FROM table
GROUP BY StoreID) T ON T.StoreID = Table.StoreID AND T.Trans = table.TransNumber

Could have trouble if 2 employees have the same sales at the same store.

 

[KLin]

Originally posted by: brandonbull

Originally posted by: KB
I have done this all too many times and always forget how at first. But its something like:


SELECT table.StoreID, table.EmployeeID, table.TransNumber
FROM table
INNER JOIN
(SELECT StoreID, MAX(TransNumber) As Trans
FROM table
GROUP BY StoreID) T ON T.StoreID = Table.StoreID AND T.Trans = table.TransNumber

Could have trouble if 2 employees have the same sales at the same store.

It should account for ties IMO.

 

[JasonCoder]

Free homework done here forum?