sql query help!

petey84

New member
Local time
Today, 11:29
Joined
Jun 28, 2008
Messages
3
I have a table called ORDERS

In the table are the following columns:

DATE ITEM
01/01/2008 RED
02/02/2007 RED
10/01/2008 BLUE
12/01/2008 RED
05/07/2007 BLUE
03/03/2008 BLUE
04/04/2008 RED



From the table, i need a sql query to show the latest TWO dates for EACH item.

So the output of the query should give me the following:

DATE ITEM
03/03/2008 BLUE
10/01/2008 BLUE
04/04/2008 RED
12/01/2008 RED
 
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).
 
Hi,

This only gives me output for one item, i need the two latest dates for EACH item.

PLEASE HELP..
 
Can you show the live SQL with which you are currently testing?
 
Hi -

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
;

HTH - Bob
 
Thanks Raskew this worked great, just a few questions, what does DISTINCT and LEFT JOIN actually do?



ps. thanks everyone else too
 
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. :-)
 
Try removing DISTINCT from the SQL and you'll see what it does. The LEFT

JOIN, in this case, could be replaced with INNER JOIN with no dire effect.

Bob
 
Leigh -

I agree to an extent. As you pointed out, the fact that I wasn't ordering on

a PK requires the DISTINCT clause to avoid the numerous artificial rows,

which Petey will see if he tries removing DISTINCT from the SQL. Fact is, the

query I provided was written long ago and was proven to return the desired

results.

I've attempted to convert your example to Northwind's Orders table (OrderID

is the PK), but haven't been successful. Maybe you'd provide a working

example.


Best wishes - Bob

ADDED: I take it back. OrderID is an autonumber field, no duplicates, but it's not designated as PK.
 
Last edited:
Hi Bob.

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. :-)

Cheers!
 
Leigh -

Shazaam--there it is and there's no doubt that it processes faster!

Thanks for that!

Showing my ignorance, please talk about

ORDER BY 3, 2 DESC...

I see it working but have never seen--nor found reference to--that approach.

Best wishes -- Bob

Added: Wow! As I play with this, I continue to be more impressed!
 
Last edited:
Showing my ignorance, please talk about

ORDER BY 3, 2 DESC...

I see it working but have never seen--nor found reference to--that approach.

Check SQL 92 documentation.

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).
 
Hi George -

Thanks for that. I understand the "DESC", it's the use of 3, 2 that had

me baffled. Just didn't realize that you could refer to fields based upon their

original position in the "SELECT BY" clause.

Obviously I've not read SQL 92 documentation. Can you point to it?


Best Wishes - Bob
 
Last edited:
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---
 

Users who are viewing this thread

Back
Top Bottom