Use of rownum function in a select
-
Hi
I need to get the first row from a subselect. Have read a couple posts on this topic, but not sure I get it.
Please find below the code.
What I need is that the route subselect only returns the first record for every route.
SELECT
(SELECT OM.ord_route, rownum r
FROM Trans T, Order OM
WHERE T."From license number." = T1."From license number."
AND T."Transaction type." = 014
AND OM.ord_key = T.ORD_KEY
AND (T."Date record was created." > T1."Date record was created." and T."Date record was created." < T1."Date record was created." + 1)
and r = 1
) as route,
T1.TO_ITEM,
T1.FROM_LOCATION,
T1.TO_LOCATION,
...............
FROM Trans as T1
WHERE
T1."From license number." like '4%'
Any ideas?
BR
Dan
-
Use
ROW_NUMBER() OVER(PARTITION BY OM.ord_route ORDER BY ...)____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
-- use proper join syntax-- don't use double-quotes for identifiers unless absolutely necessary
-- write queries using TOP 1...ORDER BY and ROW_NUMBER() and choose quickest of the two
-- CROSS APPLY is often more readable than correlated subquery in SELECT list.
SELECT
x.[Route],
T1.TO_ITEM,
T1.FROM_LOCATION,
T1.TO_LOCATION,
...............
FROM Trans as T1
CROSS APPLY (
SELECT TOP 1
[Route] = OM.ord_route
FROM Trans T
INNER JOIN [Order] OM
ON OM.ord_key = T.ORD_KEY
WHERE T.[From license number.] = T1.[From license number.]
AND T.[Transaction type.] = 014
AND (T.[Date record was created.] > T1.[Date record was created.]
AND T.[Date record was created.] < T1.[Date record was created.] + 1)
ORDER BY ...
) x
WHERE T1.[From license number.] like '4%'
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work: I have never used cross apply before. Will have to study that one.
I see you placed an group by...
Is this needed or optional.
I am trying your version now, withoute hte group by.
It seemes to query, bit slow maybe, I will have to evaluate when finished.
BR
Dan
-
@DJ (4/30/2013)
ChrisM@Work: I have never used cross apply before. Will have to study that one.I see you placed an group by...
Is this needed or optional.
I am trying your version now, withoute hte group by.
It seemes to query, bit slow maybe, I will have to evaluate when finished.
BR
Dan
There's no GROUP BY in my query.
Can you show your entire query? I'd like to see if the reference to trans in the inner query is required.
The performance will depend upon available indexes - in other words if it's poor, then consider adding or changing index(es) to support seeks in the inner select. Changing indexes will be easier if there's only one table in the inner select.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
My bad 🙂 I ment Order by, not group by.
Here is the complete code
SELECT
x.[Route],
T1."To license number. (/TO_LIC)" as TO_LIC,
T1."From license number." AS FROM_LIC,
T1.TO_ITEM,
T1.FROM_LOCATION,
T1.TO_LOCATION,
T1."Transaction type." AS TransType,
T1."Populates by TRANS_NO_SEQ to generate a uniqu" as TransNo,
T1."ORD_NO" as OrderNO,
T1."Refers to ORD_KEY in ORD_MAST." as ORD_KEY,
T1."References LOAD_ID in ORD_MAST." as Load_ID_ORD_MAST,
T1."Date record was created.",
CASE
WHEN (T1.to_location like 'INN_MONO%BANE%' OR T1.to_location like 'INN_MONO_ORM%')
THEN 'INFEED'
WHEN (T1.to_location like 'INN_MONO%' OR T1.to_location like 'PRE_MONO_ORM%')
THEN 'FLOOR'
WHEN (T1.to_location like 'TROLLEY%')
THEN 'TROLLEY'
WHEN T1.to_location like 'STG%' OR T1.to_location like 'UT%GULV'
THEN 'STAGED'
WHEN (T1.to_location like 'UT_RC_AVSLAG%' OR T1.to_location like 'UT_OPPLAST%')
THEN 'OUTFEED'
ELSE 'UNKNOWN'
END as Category
FROM TRANS as T1
CROSS APPLY (
SELECT TOP 1
[Route] = OM.ord_route
FROM TRANS T
INNER JOIN ORDER OM
ON OM.ord_key = T."Refers to ORD_KEY in ORD_MAST."
WHERE T.[From license number.] = T1.[From license number.]
AND T.[Transaction type.] = 014
AND (T.[Date record was created.] > T1.[Date record was created.]
AND T.[Date record was created.] < T1.[Date record was created.] + 1)
--ORDER BY ...
) x
WHERE T1."From license number." like '4%' and
T1."From license number." not like '49%' and
T1.to_item not like 'Z%'
AND (
T1.to_location like 'INN_MONO%'
OR(T1.from_location like 'INN_MONO%' AND T1.to_location like 'INN_MONO%BANE%'
OR(( T1.from_location like 'INN_MONO%BANE%' OR T1.from_location like 'INN_MONO_ORM%' OR T1.from_location like 'LOC_MG60001' AND T1.to_location like 'TROLLEY%')
OR( T1.from_location like 'TROLLEY%' AND ( T1.to_location like 'UT_OPPLAST&' OR T1.to_location like 'UT_RC_AVSLAG%')
)
OR ((T1.from_location like 'UT_OPPLAST%' OR T1.from_location like 'UT_RC_AVSLAG%' OR T1.from_location like 'INN_MONO%')
AND(T1.to_location like 'STG%' OR T1.to_location like 'UT_OPPLAST%GULV' )
)
OR (T1.from_location like 'FULLRC_CO_EOL%' AND T1.to_location like 'INN_MONO_ORM%')
OR (T1.from_location like 'PRE_MONO_ORM%' AND T1.to_location like 'INN_MONO_ORM%')
))
)
-
You need the ORDER BY, otherwise what TOP returns is unpredictable.
You also need the reference to table trans in the subselect because the [order] table is filtered by a different set of trans rows than the main query.
What indexes do you have on both tables? Can you post the actual execution plan?
SELECTx.[Route],
T1.[To license number. (/TO_LIC)] as TO_LIC,
T1.[From license number.] AS FROM_LIC,
T1.TO_ITEM,
T1.FROM_LOCATION,
T1.TO_LOCATION,
T1.[Transaction type.] AS TransType,
T1.[Populates by TRANS_NO_SEQ to generate a uniqu] as TransNo,
T1.[ORD_NO] as OrderNO,
T1.[Refers to ORD_KEY in ORD_MAST.] as ORD_KEY,
T1.[References LOAD_ID in ORD_MAST.] as Load_ID_ORD_MAST,
T1.[Date record was created.],
CASE
WHEN (T1.to_location like 'INN_MONO%BANE%' OR T1.to_location like 'INN_MONO_ORM%')
THEN 'INFEED'
WHEN (T1.to_location like 'INN_MONO%' OR T1.to_location like 'PRE_MONO_ORM%')
THEN 'FLOOR'
WHEN (T1.to_location like 'TROLLEY%')
THEN 'TROLLEY'
WHEN T1.to_location like 'STG%' OR T1.to_location like 'UT%GULV'
THEN 'STAGED'
WHEN (T1.to_location like 'UT_RC_AVSLAG%' OR T1.to_location like 'UT_OPPLAST%')
THEN 'OUTFEED'
ELSE 'UNKNOWN'
END as Category
FROM TRANS as T1
CROSS APPLY (
SELECT TOP 1
[Route] = OM.ord_route
FROM TRANS T
INNER JOIN [ORDER] OM
ON OM.ord_key = T.[Refers to ORD_KEY in ORD_MAST.]
WHERE T.[From license number.] = T1.[From license number.]
AND T.[Transaction type.] = 014
AND (T.[Date record was created.] > T1.[Date record was created.]
AND T.[Date record was created.] < T1.[Date record was created.] + 1)
-- ORDER BY ...
) x
WHERE T1.[From license number.] like '4%'
and T1.[From license number.] not like '49%'
and T1.to_item not like 'Z%'
AND (
T1.to_location like 'INN_MONO%'
OR (T1.from_location like 'INN_MONO%' AND T1.to_location like 'INN_MONO%BANE%'
OR (( T1.from_location like 'INN_MONO%BANE%' OR T1.from_location like 'INN_MONO_ORM%' OR T1.from_location like 'LOC_MG60001' AND T1.to_location like 'TROLLEY%')
OR ( T1.from_location like 'TROLLEY%' AND ( T1.to_location like 'UT_OPPLAST&' OR T1.to_location like 'UT_RC_AVSLAG%')
)
OR ((T1.from_location like 'UT_OPPLAST%' OR T1.from_location like 'UT_RC_AVSLAG%' OR T1.from_location like 'INN_MONO%')
AND (T1.to_location like 'STG%' OR T1.to_location like 'UT_OPPLAST%GULV' )
)
OR (T1.from_location like 'FULLRC_CO_EOL%' AND T1.to_location like 'INN_MONO_ORM%')
OR (T1.from_location like 'PRE_MONO_ORM%' AND T1.to_location like 'INN_MONO_ORM%')
))
)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply