Hi. (And welcome).
You should have a field (or more - but ideally one) which comprises the primary key.
(It's often just an autonumber). But a unique identifier for the row regardless.
With a PK this is trivial (and more efficient).
(And Date is a reserved word - hopefully it's just you abstracting the real name? ;-)
Code:
SELECT T1.PKField, T1.[DATE], T1.ITEM
FROM ORDERS T1
WHERE PKField In
(SELECT TOP 2 T2.PKField
FROM ORDERS T2
WHERE T2.ITEM = T1.ITEM
ORDER BY T2.[DATE] DESC)
ORDER BY 3, 2 DESC
(I don't know what your date format is by location - I'm guessing your dates need to descend in the final result dd/mm/yyyy).
Little different approach. Here's a working query against Northwind's Orders
table. It returns the top 2 OrderDates for each employee. You should be able
to easily adapt it by replacing field names to match your own.
Code:
SELECT DISTINCT
Orders.EmployeeID
, Orders.OrderDate
FROM
Orders AS t
LEFT JOIN
Orders
ON
t.EmployeeID = Orders.EmployeeID
WHERE
(((Orders.OrderDate) In (
SELECT
Top 2 [OrderDate]
FROM
Orders
WHERE
[EmployeeID]= t.[EmployeeID]
ORDER BY
[OrderDate] DESC)))
ORDER BY
Orders.EmployeeID
, Orders.OrderDate DESC
Hi Bob.
I'm afraid I'm not quite seeing how that's a better approach?
Different slightly yes - but with drawbacks all its own.
The lack of the use of a PK - and it consequently needs to have a different way of deciding what appears.
I'll expand a bit...
The DISTINCT clause is to prevent a bunch of artificial duplicate rows - brought about with the Join on the FK between the table and it's aliased copy.
What's the benefit to doing this though - as we limit the results by the corrolated subquery and the DISTINCT clause anyway.
It's adding a lot of overhead.
The different results I speak of are that, by adding the DISTINCT clause - if you had a tie (of two or more dates) but returned only the top 2 - the DISTINCT would see only one row displayed.
If that's what you want then fine (the DISTINCT clause can always be added to the more standard query).
But it doesn't allow the displaying of more than one result if a tie does exist.
It could be chopped down to the more efficient
Code:
SELECT DISTINCT
t.EmployeeID
, t.OrderDate
FROM
Orders AS t
WHERE
(((t.OrderDate) In (
SELECT
Top 2 [OrderDate]
FROM
Orders
WHERE
[EmployeeID]= t.[EmployeeID]
ORDER BY
[OrderDate] DESC)))
ORDER BY
t.EmployeeID
, t.OrderDate DESC
And get the same results - but still with the same limitations.
The query I offered is absolutely standard - but requires a Primary Key field to allow for unique results (without the comparative inefficiency).
It can be made a bit more discerning with more ordering clauses
Code:
SELECT T1.PKField, T1.[DATE], T1.ITEM
FROM ORDERS T1
WHERE PKField In
(SELECT TOP 2 T2.PKField
FROM ORDERS T2
WHERE T2.ITEM = T1.ITEM
ORDER BY T2.[DATE] DESC, T2.PKField )
ORDER BY 3, 2 DESC
If you post what your implementation of the earlier query was I'll have a look. If the problem is that you don't have a PK field in the table - then there are bigger problems ahead.
I suppose it depends on one's interpretation of "working" lol
If made aware of the actual field names in a question - I happily provide a verbatim query for the situation of the thread (it's always my preference to do so - even though I do firmly believe in questioners learning by doing - even though it doesn't necessarily teach the underlying principals by changing names).
However - a working (i.e. verbatim) solution for the Northwind db? OK:
Code:
SELECT T1.OrderID, T1.OrderDate, T1.EmployeeID
FROM ORDERS T1
WHERE T1.OrderID In
(SELECT TOP 2 T2.OrderID
FROM ORDERS T2
WHERE T2.EmployeeID = T1.EmployeeID
ORDER BY T2.OrderDate DESC, T2.OrderID )
ORDER BY 3, 2 DESC
Much more efficient.
I can fully appreciate, though, that you've had something that works on day one - and tend to just use it because it works.
I love to poke and push things. Sometimes it's a risk to push something that's working I know. That's what I love about the world of IT. Copy Paste Play Destroy Redo
P.S. OrderID is the PK field in Northwind as I look at it. Odd. Doesn't matter though - as long as it's a unique identifier.
The number refers to the column number and the word "DESC" says to sort in reverse order.
So, order by 3, 2 DESC means, order this result set by the third column in ascending order (EmployeeID) and if there's more than one record with the same employee id, order on the second column in reverse order (OrderDate).
It is specific to the implementation. This is from the SQL Books Online documentation for SQL Server 2000:
ORDER BY Clause
Specifies the sort for the result set. The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. Syntax
[ ORDER BY { order_by_expression [ ASC | DESC ] } [ ,...n] ] Arguments
order_by_expression
Specifies a column on which to sort. A sort column can be specified as a nameor column alias (which can be qualified by the table or view name), an expression, or a nonnegative integer representing the position of the name, alias, or expression in select list.
---snip---
