SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Use of rownum function in a select


Use of rownum function in a select

Author
Message
@DJ
@DJ
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 189
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, [color=#F90A0A]rownum r[/color]
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)
[color=#F90A0A]and r = 1[/color]
) 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
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6640 Visits: 25623
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




ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42687 Visits: 20015
-- 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 Shaw

For 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
Exploring Recursive CTEs by Example Dwain Camps
@DJ
@DJ
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 189
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
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42687 Visits: 20015
@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 Shaw

For 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
Exploring Recursive CTEs by Example Dwain Camps
@DJ
@DJ
SSC-Addicted
SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)SSC-Addicted (437 reputation)

Group: General Forum Members
Points: 437 Visits: 189
My bad Smile 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%')
))
)
ChrisM@Work
ChrisM@Work
SSC-Forever
SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)SSC-Forever (42K reputation)

Group: General Forum Members
Points: 42687 Visits: 20015
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?

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%')
))
)



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For 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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search