SQL Help Needed :(

[KempStar]

Hello

I have been trying to complete an SQL query, but am not having much luck. Im sure its very simple, but im not so great with SQL

I have the following query:


(SELECT T1.Dscription, T0.DocDate, T2.ItmsGrpCod, T0.DocDate AS DocDate1 FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'CKtest' AND T2.ItmsGrpCod = 100)
UNION ALL
(SELECT T4.Dscription, T3.DocDate, T5.ItmsGrpCod, T3.DocDate AS DocDate1 FROM OINV T3 INNER JOIN INV1 T4 ON T3.DocEntry = T4.DocEntry INNER JOIN OITM T5 ON T4.ItemCode = T5.ItemCode
WHERE T3.CardCode = 'CKtest' AND T5.ItmsGrpCod = 100)
ORDER BY DocDate1

Which returns:

Dscription DocDate ItmsGrpCod DocDate1
Shirt 04.01.05 100 04.01.05
Racket 09.01.05 100 09.01.05



What I need to do next, is take the most recent(highest date) result from that query, and return it like this:

Last purchased Item: (Dscription), On : (DocDate1)



So effectively what I am asking is how can you do a select statement, based on the results of another select statement?

Many thanks in advance if you can help

[kona]

What database server are you using?
If you're using SQL Server 2000, or Oracle there are some commands that will make this look quite simple otherwise it will be a bit messy.

[dewhal]

Looks quite messy, surely there is an easier way of doing that. If not try:

select top 1 max(docdate), dscription
from (SELECT T1.Dscription as dscription, T0.DocDate, T2.ItmsGrpCod, T0.DocDate AS DocDate1 FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE T0.CardCode = 'CKtest' AND T2.ItmsGrpCod = 100)
UNION ALL
(SELECT T4.Dscription as dscription, T3.DocDate, T5.ItmsGrpCod, T3.DocDate AS DocDate1 FROM OINV T3 INNER JOIN INV1 T4 ON T3.DocEntry = T4.DocEntry INNER JOIN OITM T5 ON T4.ItemCode = T5.ItemCode
WHERE T3.CardCode = 'CKtest' AND T5.ItmsGrpCod = 100)
ORDER BY DocDate1 )
group by dscription

[kona]

If you use max() then isn't "top 1" redundant?
So saying just "select max(docdate), description..." would do the same thing.
Also since you are only selecting one row using the max(docdate), the "order by" clause can be removed.
Finally again since you select 1 row you don't need to fetch the docdate column twice or use the synonyms.

So I think the query would look like:
select max(docdate), description
from (
<Kempstar's original query but without the order by>
)
group by description

Don't have a DB here to test this though.